CL program to refresh SAP instance

[Former Member]

Hello everyone,

We have a CL program to refresh SAP instance with limited manual work. The procedure is close to the “database copy as of 46B” file posted by Volker in the SAPoniSeries Yahoo group. For each refresh, we could just change the entry in WRKJOBSCDE, defined by TSTOFR, then call a few transactions like STMS, SE38 (SDB4GEN and RSDB4GEN), SCC4, and SM59 after the CL program is finished.

The program is not perfect, nor fits all situations – indicated in the comments below. Any help to make the program better is highly appreciated.

Another thing we haven't worked it out is to add DATE info into SQL statements – currently it is a static source file. Potential solutions may use RPG or JAVA code, or edit a stream file with QSH command then copy it to the SQL source file. Does anyone have a straight-forward solution?

Thank you,

Victor Lin

• Main CL program ***********

/* REFRESH SAP TST INSTANCE */

PGM

CALL R3TST400/R3INLPGM

/* Stop SAP TST */

STOPSAP SID(TST) INSTANCE(05) WAIT(*YES) +

WAITTIME(9999) ENDSBS(*YES)

DLTR3PKG SID(TST) JOBS(12)

/* Hold the daily backup job */

HLDJOBSCDE JOB(DLYBACKUP)

/* Delete R3TSTDATA Library */

CHGCURLIB CURLIB(QGPL)

DLTLIB LIB(R3TSTDATA)

/* Refresh R3TSTDATA library through tape */

/* Note: on the condition that only one tape is needed to save the whole R3PRDDATA library */

SAVLIB LIB(R3PRDDATA) DEV(TAP01) ENDOPT(*REWIND) +

SAVACT(*SYNCLIB) SAVACTWAIT(99999)

RSTLIB SAVLIB(R3PRDDATA) DEV(TAP01) SEQNBR(1) +

ENDOPT(UNLOAD) MBROPT(ALL) +

ALWOBJDIF(*ALL) RSTLIB(R3TSTDATA)

MONMSG MSGID(CPF0000)

/* We used to end Mimix Data Group then CPYLIB when multiple tapes were needed for a full backup of R3PRDDATA. Could anyone suggest a better solution? */

/* Release the daily backup job */

RLSJOBSCDE JOB(DLYBACKUP)

/* Create Journal QSQJRN */

/* Note: We configure the journal of TST to be deleted automatically by the system */

CRTSAVF FILE(QTEMP/R3TSTJRNSV)

SAVLIB LIB(R3TSTJRN) DEV(*SAVF) SAVF(QTEMP/R3TSTJRNSV)

DLTLIB LIB(R3TSTJRN)

CRTLIB LIB(R3TSTJRN) ASP(2)

CRTJRNRCV JRNRCV(R3TSTJRN/QSQJRN0001) THRESHOLD(1600000)

CRTJRN JRN(R3TSTDATA/QSQJRN) +

JRNRCV(R3TSTJRN/QSQJRN0001) +

MNGRCV(SYSTEM) DLTRCV(YES) +

RCVSIZOPT(*MAXOPT2 *RMVINTENT)

/* Correct the Catalog Views */

CALL QSYS2/SQSXRLF PARM(DLT R3TSTDATA)

CALL QSYS2/QSQXRLF PARM(CRT R3TSTDATA)

/* Authority Change */

GRTOBJAUT OBJ(R3TSTDATA) OBJTYPE(*LIB) USER(TST05) +

AUT(*CHANGE)

CHGOBJOWN R3TSTDATA *LIB TSTOWNER

/* Start Journaling */

AS4FIXFILE DBLIB(R3TSTDATA) CHGOWN(*YES) +

ENDJRN(YES) STRJRN(YES) CHGFILE(*NO)

/* Chang the logo image from a previous copy of table SSM_CUST */

/* Might be better to just do it in SQL statement */

CPYF FROMFILE(SAPR3MOD/SSM_CUST) +

TOFILE(R3TSTDATA/SSM_CUST) MBROPT(*REPLACE)

/* Run SQL statements for other changes */

/* The SQL source file is copied below */

RUNSQLSTM SRCFILE(SAPR3MOD/QSQLSRC) SRCMBR(REFRESHTST)

/* Copy the import buffer from PRD to TST */

QSH CMD('RM /USR/SAP/TRANS/BUFFER/TST')

QSH CMD('CP /USR/SAP/TRANS/BUFFER/PRD +

/USR/SAP/TRANS/BUFFER/TST')

/* Reapply the kernel */

FIXR3OWNS LIB(R3640TOPT)

APYR3KRN SID(TST) KRNLIB(R3640TOPT)

/* Start SAP TST */

SBMJOB CMD(CALL PGM(SAPR3MOD/STARTTST)) +

JOB(STARTTST) USER(TSTOFR) MSGQ(QSYSOPR)

/* Note: not sure whether it is the best way to start TST... */

ENDPGM

• SAPR3MOD/QSQLSRC/REFRESHTST ************

/* REMOVE ALL BACKGROUND JOBS EXCEPT SCHEDULED BY DDIC */

DELETE FROM R3TSTDATA/TBTCO

WHERE RELUNAME <> 'DDIC';

DELETE FROM R3TSTDATA/TBTCS

WHERE SDLUNAME <> 'DDIC';

DELETE FROM R3TSTDATA/TBTCP

WHERE SDLUNAME <> 'DDIC';

/* PRINTERS SETTING */

UPDATE R3TSTDATA/TSP03C

SET PAMSSERVER = 'TSTSERVER_TST_05'

WHERE PAMSSERVER = 'PRDSERVER_PRD_10';

UPDATE R3TSTDATA/TSP03D

SET PAMSSERVER = 'TSTSERVER_TST_05'

WHERE PAMSSERVER = 'PRDSERVER_PRD_10';

/* Should be able to add SM59 changes here also */

• SAPR3MOD/STARTTST *******

PGM

CALL R3TST400/R3INLPGM

STARTSAP SID(TST) INSTANCE(05)

ENDPGM

• End *************

[Former Member · Accepted solution]

>>>>

Another thing we haven't worked it out is to add DATE info into SQL statements – currently it is a static source file. Potential solutions may use RPG or JAVA code, or edit a stream file with QSH command then copy it to the SQL source file. Does anyone have a straight-forward solution?

<<<<<

DB2 UDB for iSeries SQL has "special registers" named CURRENT DATE and CURRENT TIME for U to use. They serve like program variables to be refered to in SQL program. Example :

UPDATE PROJECT

SET PRENDATE = CURRENT DATE

WHERE PROJNO = 'MA2111';

SELECT CLASS_CODE FROM CL_SCHED

WHERE STARTING > CURRENT TIME AND DAY = 3 ;

These are documented in SQL Reference of DB2 UDB for iSeries.

Hope this help.

Satid S.

IBM Thailand