Can I do this with SQL and JCL: Read a DB2 catalog table, extract DB & TS names, use the string to start tablespaces in ACCESS(UT)"
HI all,
My current set involves always having to add/delete tablespaces every time there is a change. As you can see, I have had to hard code the tablespaces that I need as shown below:
//UTL1000U EXEC PGM=IKJEFT01,DYNAMNBR=20 00012003
//* 00012003
//* START TABLESPACES IN UTILITY MODE FOR EXCLUSIVITY 00012003
//* 00012003
//SYSTSPRT DD SYSOUT=* 00012003
//SYSPRINT DD SYSOUT=* 00012003
//SYSTSIN DD * 00012003
DSN SYSTEM(DBO1) 00012003
-START DB(PRD1FFEV1) SPACENAM(PD1SCAM1) ACCESS (UT)
-START DB(PRD1FFEV1) SPACENAM(PD1SCJR1) ACCESS (UT)
-START DB(PRD1FFEV1) SPACENAM(PD1SDNF1) ACCESS (UT)
-START DB(PRD1FFEV1) SPACENAM(PD1SGCT1) ACCESS (UT)
END
What I will like to do is extract and dynamically build the START string, call the DB2 START command. I will like to know if I can do the following and if anyone can show me how best to achieve my goal of dynamically start my tablespaces in ACCESS (UT) mode?
What I have in mind is as follows:
//UTL1000U EXEC PGM=IKJEFT01,DYNAMNBR=20 00012003
//* 00012003
//* START TABLESPACES IN UTILITY MODE FOR EXCLUSIVITY 00012003
//* 00012003
//SYSTSPRT DD SYSOUT=* 00012003
//SYSPRINT DD SYSOUT=* 00012003
//SYSTSIN DD * 00012003
DSN SYSTEM(DBO1)
Select " DB(||dbname||") SPACENAM("tsname") ACCESS (UT)" as start_db_tsname
from sysibm.systablespace
where dbname = "PRD1FFEV1"
-START start_db_tsname
END
Can it be done this way? What other ways can I achieve the same purpose?
Thanks guys
My current set involves always having to add/delete tablespaces every time there is a change. As you can see, I have had to hard code the tablespaces that I need as shown below:
//UTL1000U EXEC PGM=IKJEFT01,DYNAMNBR=20 00012003
//* 00012003
//* START TABLESPACES IN UTILITY MODE FOR EXCLUSIVITY 00012003
//* 00012003
//SYSTSPRT DD SYSOUT=* 00012003
//SYSPRINT DD SYSOUT=* 00012003
//SYSTSIN DD * 00012003
DSN SYSTEM(DBO1) 00012003
-START DB(PRD1FFEV1) SPACENAM(PD1SCAM1) ACCESS (UT)
-START DB(PRD1FFEV1) SPACENAM(PD1SCJR1) ACCESS (UT)
-START DB(PRD1FFEV1) SPACENAM(PD1SDNF1) ACCESS (UT)
-START DB(PRD1FFEV1) SPACENAM(PD1SGCT1) ACCESS (UT)
END
What I will like to do is extract and dynamically build the START string, call the DB2 START command. I will like to know if I can do the following and if anyone can show me how best to achieve my goal of dynamically start my tablespaces in ACCESS (UT) mode?
What I have in mind is as follows:
//UTL1000U EXEC PGM=IKJEFT01,DYNAMNBR=20 00012003
//* 00012003
//* START TABLESPACES IN UTILITY MODE FOR EXCLUSIVITY 00012003
//* 00012003
//SYSTSPRT DD SYSOUT=* 00012003
//SYSPRINT DD SYSOUT=* 00012003
//SYSTSIN DD * 00012003
DSN SYSTEM(DBO1)
Select " DB(||dbname||") SPACENAM("tsname") ACCESS (UT)" as start_db_tsname
from sysibm.systablespace
where dbname = "PRD1FFEV1"
-START start_db_tsname
END
Can it be done this way? What other ways can I achieve the same purpose?
Thanks guys
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi guys,
I have tried to use moni_sabag's recommendation. it worked up to step2 where I need to issue the -start command to put tablespaces in UT mode. In short step 2 is not working and here is how I have done it:
//* FIND ALL TABLESPACES FOR SPECIFIED TRACK
//*
//UNLOADTS EXEC PGM=IKJEFT01,DYNAMNBR=20
//STEPLIB DD DSN=SYS2.DB2.TEST.SDSNLOAD
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSREC00 DD DSN=&&OUT,DISP=(NEW,CATLG,
// SPACE=(1,(1800,400)),AVGRE
//SYSPUNCH DD DUMMY
//SYSTSIN DD *
DSN SYSTEM(DBO1) RETRY(3)
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIB81) -
LIB('SYS2.DB2.TEST.RUNLIB.
END
/*
//SYSIN DD *
SELECT '-START DB('||DBNAME||') SPACENAME('||NAME||') ACCESS (UT);'
FROM SYSIBM.SYSTABLESPACE
WHERE DBNAME LIKE 'OG1DD%'
AND NAME LIKE 'OG1SC%'
;
//*
//* START TABLESPACES IN UTILITY MODE FOR EXCLUSIVITY
//*
//UTL1000U EXEC PGM=IKJEFT01,DYNAMNBR=20
//STEPLIB DD DSN=SYS2.DB2.TEST.SDSNLOAD
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSPUNCH DD DUMMY
//* DD DISP=SHR,DSN=&&OUT ACCESS (UT)
//SYSTSIN DD *
DSN SYSTEM(DBO1)
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIB81) LIB('SYS2.DB2.TEST.RUNLIB.
END
/*
//SYSIN DD *
// DD DISP=SHR,DSN=&&OUT
END
--------------------------
OUTPUT Message
1 DSNT490I SAMPLE DATA UNLOAD PROGRAM
0 DSNT505I DSNTIAUL OPTIONS USED: SQL
0 DSNT503I UNLOAD DATA SET SYSPUNCH RECORD LENGTH SET TO 80
0 DSNT506I INPUT STATEMENT WAS NOT A FULL SELECT ON A SINGLE TABLE. LOAD STATEME
0 DSNT503I UNLOAD DATA SET SYSREC00 RECORD LENGTH SET TO 86
0 DSNT504I UNLOAD DATA SET SYSREC00 BLOCK SIZE SET TO 27950
0 DSNT495I SUCCESSFUL UNLOAD 4 ROWS OF TABLE TBLNAME
OUTPUT FILE LAYOUT
-START DB(OG1DDEV1) SPACENAME(OG1SCAM1) ACCESS (UT);
-START DB(OG1DDEV1) SPACENAME(OG1SCJR1) ACCESS (UT);
-START DB(OG1DDEVL) SPACENAME(OG1SCAM1) ACCESS (UT);
-START DB(OG1DDEVL) SPACENAME(OG1SCJR1) ACCESS (UT);
ERROR MESSAGE AT STEP 3 - START DB TS IN UT MODE
ERROR MESSAGE
DSNT490I SAMPLE DATA UNLOAD PROGRAM
DSNT503I UNLOAD DATA SET SYSPUNCH RECORD LENGTH SET TO 80
DSNT493I SQL ERROR DURING SQL STATEMENT PREPARE , TABLE -START
DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL " ". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: ( FINAL TABLE <IDENTIFIER>
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 502 0 0 -1 21 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'000001F6' X'00000000' X'00000000' X'FFFFFFFF' X'0000
INFORMATION
DSNT493I SQL ERROR DURING SQL STATEMENT PREPARE , TABLE -START
DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL " ". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: ( FINAL TABLE <IDENTIFIER>
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 502 0 0 -1 21 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'000001F6' X'00000000' X'00000000' X'FFFFFFFF' X'0000
INFORMATION
DSNT493I SQL ERROR DURING SQL STATEMENT PREPARE , TABLE -START
DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL " ". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: ( FINAL TABLE <IDENTIFIER>
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 502 0 0 -1 21 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'000001F6' X'00000000' X'00000000' X'FFFFFFFF' X'0000
INFORMATION
DSNT493I SQL ERROR DURING SQL STATEMENT PREPARE , TABLE -START
DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL " ". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: ( FINAL TABLE <IDENTIFIER>
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 502 0 0 -1 21 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'000001F6' X'00000000' X'00000000' X'FFFFFFFF' X'0000
INFORMATION
Is there anything else I can try to make this last step work?
Thanks guys
I have tried to use moni_sabag's recommendation. it worked up to step2 where I need to issue the -start command to put tablespaces in UT mode. In short step 2 is not working and here is how I have done it:
//* FIND ALL TABLESPACES FOR SPECIFIED TRACK
//*
//UNLOADTS EXEC PGM=IKJEFT01,DYNAMNBR=20
//STEPLIB DD DSN=SYS2.DB2.TEST.SDSNLOAD
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSREC00 DD DSN=&&OUT,DISP=(NEW,CATLG,
// SPACE=(1,(1800,400)),AVGRE
//SYSPUNCH DD DUMMY
//SYSTSIN DD *
DSN SYSTEM(DBO1) RETRY(3)
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIB81) -
LIB('SYS2.DB2.TEST.RUNLIB.
END
/*
//SYSIN DD *
SELECT '-START DB('||DBNAME||') SPACENAME('||NAME||') ACCESS (UT);'
FROM SYSIBM.SYSTABLESPACE
WHERE DBNAME LIKE 'OG1DD%'
AND NAME LIKE 'OG1SC%'
;
//*
//* START TABLESPACES IN UTILITY MODE FOR EXCLUSIVITY
//*
//UTL1000U EXEC PGM=IKJEFT01,DYNAMNBR=20
//STEPLIB DD DSN=SYS2.DB2.TEST.SDSNLOAD
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSPUNCH DD DUMMY
//* DD DISP=SHR,DSN=&&OUT ACCESS (UT)
//SYSTSIN DD *
DSN SYSTEM(DBO1)
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIB81) LIB('SYS2.DB2.TEST.RUNLIB.
END
/*
//SYSIN DD *
// DD DISP=SHR,DSN=&&OUT
END
--------------------------
OUTPUT Message
1 DSNT490I SAMPLE DATA UNLOAD PROGRAM
0 DSNT505I DSNTIAUL OPTIONS USED: SQL
0 DSNT503I UNLOAD DATA SET SYSPUNCH RECORD LENGTH SET TO 80
0 DSNT506I INPUT STATEMENT WAS NOT A FULL SELECT ON A SINGLE TABLE. LOAD STATEME
0 DSNT503I UNLOAD DATA SET SYSREC00 RECORD LENGTH SET TO 86
0 DSNT504I UNLOAD DATA SET SYSREC00 BLOCK SIZE SET TO 27950
0 DSNT495I SUCCESSFUL UNLOAD 4 ROWS OF TABLE TBLNAME
OUTPUT FILE LAYOUT
-START DB(OG1DDEV1) SPACENAME(OG1SCAM1) ACCESS (UT);
-START DB(OG1DDEV1) SPACENAME(OG1SCJR1) ACCESS (UT);
-START DB(OG1DDEVL) SPACENAME(OG1SCAM1) ACCESS (UT);
-START DB(OG1DDEVL) SPACENAME(OG1SCJR1) ACCESS (UT);
ERROR MESSAGE AT STEP 3 - START DB TS IN UT MODE
ERROR MESSAGE
DSNT490I SAMPLE DATA UNLOAD PROGRAM
DSNT503I UNLOAD DATA SET SYSPUNCH RECORD LENGTH SET TO 80
DSNT493I SQL ERROR DURING SQL STATEMENT PREPARE , TABLE -START
DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL " ". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: ( FINAL TABLE <IDENTIFIER>
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 502 0 0 -1 21 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'000001F6' X'00000000' X'00000000' X'FFFFFFFF' X'0000
INFORMATION
DSNT493I SQL ERROR DURING SQL STATEMENT PREPARE , TABLE -START
DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL " ". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: ( FINAL TABLE <IDENTIFIER>
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 502 0 0 -1 21 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'000001F6' X'00000000' X'00000000' X'FFFFFFFF' X'0000
INFORMATION
DSNT493I SQL ERROR DURING SQL STATEMENT PREPARE , TABLE -START
DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL " ". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: ( FINAL TABLE <IDENTIFIER>
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 502 0 0 -1 21 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'000001F6' X'00000000' X'00000000' X'FFFFFFFF' X'0000
INFORMATION
DSNT493I SQL ERROR DURING SQL STATEMENT PREPARE , TABLE -START
DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL " ". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: ( FINAL TABLE <IDENTIFIER>
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 502 0 0 -1 21 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'000001F6' X'00000000' X'00000000' X'FFFFFFFF' X'0000
INFORMATION
Is there anything else I can try to make this last step work?
Thanks guys
as i answered in the new question, in order to process the start commands you need to run tso
dsntiaul only process select statements
dsntiaul only process select statements
ASKER
Thanks guys
ASKER
I am still at home and will test your script when I get to the office. But looking at it casually, I think they may be syntax error...can you confirm that the script is ok the way it is?
Do you run the seconf step also with DSNTIAUl or run it with IKJEFT01?
Thanks