repotools -createTables SQL error DB2 zOS
I'm attempting to create the tables for the JTS database by running "repotools-jts -createTables" per Creating DB2 for z/OS database tables remotely. I completed all the pre-reqs (though not ruling out that I could have made a mistake someplace). This command fails (see log output below). What could be wrong?
Application server: Windows Server 2008
Database: DB2 on z/OS v10.1
RTC 4.0
Log output from repotools:
Application server: Windows Server 2008
Database: DB2 on z/OS v10.1
RTC 4.0
Log output from repotools:
2012-07-13 18:48:29,998 Repo Tools 2012-07-13 18:48:29,998 java.version=1.6.0 2012-07-13 18:48:29,998 java.runtime.version=pwa6460sr10fp1-20120321_01 (SR10 FP1)
2012-07-13 18:48:30,014 Provisioning using "D:\IBM\JazzTeamServer\server\conf\jts\provision_profiles".
2012-07-13 18:48:30,092 repotools-jts -createTables
2012-07-13 18:48:30,108 Jazz Foundation - Core Libraries, Version 4.0 (RJF-I20120531-1400)
2012-07-13 18:48:36,687 CRJAZ1363I Loading the configuration from "file:conf/jts/teamserver.properties".
2012-07-13 18:48:36,937 CRJAZ1779I This server is configured as a JTS.
2012-07-13 18:48:39,640 CRJAZ1365I The server is attempting to connect to the following database: "//lpar7.pfsfhq.com:5031/L7DB2I:user=xxxxxxxx;password=xxxxxxxx;"
2012-07-13 18:48:40,265 CRJAZ1364I The connection to the following database was successful:
Db Product Name: DB2
Db Product Version: DSN10015
Db URL: jdbc:db2://lpar7.pfsfhq.com:5031/L7DB2I:user=xxxxxxxx;password=xxxxxxxx;
Jdbc Driver Name: IBM DB2 JDBC Universal Driver Architecture
Jdbc Driver Version: 3.62.56
2012-07-13 18:48:41,109 CRJAZ1781W The Public URI Root has not been set. Use the server setup wizard to enter the public URI. This property is required to enforce consistent URL management in the application. The public URI represents the URL that all clients must use to access the application.
2012-07-13 18:48:43,656 CRJAZ2448I The public url property is not set. This is used to determine the running state of the server. The repotools command will skip this check and continue processing the command.
2012-07-13 18:48:43,687 Creating the tables for the database "//lpar7.pfsfhq.com:5031/L7DB2I:user=xxxxxxxx;password=xxxxxxxx;" with indices.
2012-07-13 18:48:57,782 CRJAZ0577E The following SQL query did not execute properly on the server:
CREATE VIEW JPJTS_COMMON_SNAPSHOT.SMPL_ITER AS SELECT I.ITERATION_ITEMID AS ITERATION_ITEMID, I.ITERATION_NAME AS ITERATION_NAME, I.ITERATION_ARCHIVED AS ITERATION_ARCHIVED FROM JPJTS_COMMON_SNAPSHOT.ITERATION I
com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-164, SQLSTATE=42502, SQLERRMC=JAZZPROD;JPJTS_COMMON_SNAPSHOT, DRIVER=3.62.56
...
4 answers
Trying the obvious first ........ I just googled sqlcode=-164 and it says the following
authorization-id
DOES NOT HAVE THE PRIVILEGE TO CREATE A VIEW WITH QUALIFICATION
qualifier-name
Where authid would be "JAZZPROD" and qualifier name would be "JPJTS_COMMON_SNAPSHOT.
Details can be found here. Hopefully this gives you something to go on
I was just off doing the same... thanks! Question though...
We don't want to allow the db user (JAZZPROD) to have SYSADM or DBADM permissions permanently. Can we safely provide the perms, execute repotools, and remove these perms? or does the db user require these perms for long-term runtime operations (aside from db upgrades etc)?
We don't want to allow the db user (JAZZPROD) to have SYSADM or DBADM permissions permanently. Can we safely provide the perms, execute repotools, and remove these perms? or does the db user require these perms for long-term runtime operations (aside from db upgrades etc)?
Someone more intimate with the database aspect of the products will have to answer that question specifically.
However.... reaching way back into my dark distant past ..... can't you have a user that has been granted the correct authority at the tablespace level (create table etc etc). I think things has change a bit since I use to dabble in the DB2 admin side of things and perhaps there are different level... maybe there is a schema level you can grant authority at or something like that. ..... ANyway, this may give you something to play around with while you wait for an answer to your question about removing DBADM after repotool execution.