get -104 when rexx sub jcl to rotate a partition

Victor Niu · Posted: Mon Mar 28, 2011 2:41 pm

I have a rexx to compute date and submit rotate job like this:

/*REXX*/
DB2_NAME= 'ALERTSTA'
DB2_ROATEA='2011-07-01'
DB2_ROATEB='-23.59.59.999999'
CALL ROTA_JCL
EXIT
ROTA_JCL:
QUEUE "//TBSMDB3J JOB (ACCTNUM,EXP),'PGMRNAME', "
QUEUE "// TIME=1440, "
QUEUE "// NOTIFY=&SYSUID, "
QUEUE "// REGION=6500K, "
QUEUE "// CLASS=6, "
QUEUE "// MSGCLASS=X, "
QUEUE "// MSGLEVEL=(1,1) "
QUEUE "//JOBLIB DD DSN=DSN810.SDSNLOAD,DISP=SHR "
QUEUE "//TBSMDB1 EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT) "
QUEUE "//SYSTSPRT DD SYSOUT=* "
QUEUE "//SYSPRINT DD SYSOUT=* "
QUEUE "//SYSUDUMP DD SYSOUT=* "
QUEUE "//SYSTSIN DD * "
QUEUE " DSN SYSTEM(DSTA) "
QUEUE " RUN PROGRAM(DSNTIAD) PLAN(DSNTIA81) - "
QUEUE " LIB('DSN810.DSTA.RUNLIB.LOAD') "
QUEUE " END "
QUEUE "//SYSIN DD * "
QUEUE " SET CURRENT SQLID = 'TBSM02' ; "
QUEUE " ALTER TABLE TBSM02."DB2_NAME""
QUEUE " ('"DB2_ROATEA""DB2_ROATEB||"') RESET ; "
QUEUE "/* "
"ALLOCATE FI(WRITEOUT) WRITER(INTRDR) SYSOUT(A)"
ADDRESS MVS "EXECIO "QUEUED()" DISKW WRITEOUT (FINIS"
"FREE FI(WRITEOUT)"
RETURN

when I execute this pgm and get JOB TBSMDB3J at spool,the error msg is:

DSNTIAD - SAMPLE DYNAMIC SQL PROGRAM 2.0

SET CURRENT SQLID = 'TBSM02'
SQL ERROR DURING EXECUTE IMMEDIATE
DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL " ". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: END GET SET
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHLEX SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 502 0 0 -1 1 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'000001F6' X'00000000' X'00000000' X'FFFFFFFF' X'00000001' X'00000000' SQL DIAGNOSTIC
INFORMATION

ALTER TABLE TBSM.ALERTSTA
('2011-07-01-23.59.59.999999') RESET
SQL ERROR DURING EXECUTE IMMEDIATE
DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL " ". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: END GET SET
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHLEX SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 502 0 0 -1 1 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'000001F6' X'00000000' X'00000000' X'FFFFFFFF' X'00000001' X'00000000' SQL DIAGNOSTIC
INFORMATION

Pls can anyone give some advice?

enrico-sorichetti · Posted: Mon Mar 28, 2011 3:12 pm

I see that You like to complicate things!

the normal approach would be
1) prepare and submit a straight jcl for the process to be used later as a model
2) build a wrapper around it! ( building the jcl according to the experience from step 1)

what happened when You submitted a simple plain jcl ???

expat · Posted: Mon Mar 28, 2011 3:16 pm

And I would suggest using ISPF file tailoring rather than building JCL instream. IMO far easier to find and maintain.

Kjeld · Posted: Mon Mar 28, 2011 3:54 pm

The SQL code seems to arise from you have forgot to add a semicolon at the end of your DB2 statements. When you use the SPUFI processor this is mandatory.

Victor Niu · Posted: Tue Mar 29, 2011 7:23 am

yes,I can raise another method as expat said to generated a member with jcl,then change and sub the member dynamically by rexx.This is a db maintenance job,and running by SA timer everyday.Rotating will be done according to the computed result date by rexx.

But I hope to know the reason,why failed?

I check the JCL in spool by command "SJ",now two semicolon exist:

//TBSMDB3J JOB (ACCTNUM,EXP),'PGMRNAME',
// TIME=1440,
// NOTIFY=&SYSUID,
// REGION=6500K,
// CLASS=6,
// MSGCLASS=X,
// MSGLEVEL=(1,1)
//JOBLIB DD DSN=DSN810.SDSNLOAD,DISP=SHR
//TBSMDB1 EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT)
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DSTA)
RUN PROGRAM(DSNTIAD) PLAN(DSNTIA81) -
LIB('DSN810.DSTA.RUNLIB.LOAD')
END
//SYSIN DD *
SET CURRENT SQLID = 'TBSM02';
ALTER TABLE TBSM.ALERTSTA
('2011-07-01-23.59.59.999999') RESET;
/*

Kjeld · Posted: Tue Mar 29, 2011 12:15 pm

Victor Niu · Posted: Tue Mar 29, 2011 2:11 pm

the syntax seems correct from joblog:

//SYSIN DD *
SET CURRENT SQLID = 'TBSM02';
ALTER TABLE TBSM.ALERTSTA
('2011-07-01-23.59.59.999999') RESET;
/*

enrico-sorichetti · Posted: Tue Mar 29, 2011 2:15 pm

we are getting into a loop here
again
run a good old plain jcl for the rotate process
once You have found the correct jcl/parms then and only the You should try to generate them dynamically

PeterHolland · Posted: Wed Mar 30, 2011 7:11 pm

Hello Victor,

has this anything to do with TBSM? And if yes which flavor of TBSM?
When i worked with TBSM there didnt exist a DB2 table at the mainframe
side, it was totally Windows SQL Server orientated.

Kjeld · Posted: Thu Mar 31, 2011 1:23 pm

According to the original post, the syntax error is reported already for the SET CURRENT SQLID statement. I assume the error is on the punched SQL altogether. Try running this in a plain JCL submit. Once you have established the rigth code, embed it in your punching REXX.

Anyway, you ALTER TABLE statement should be followed by additional keywords describing the alter operation, like ROTATE PARTITION. Check the SQL reference manual again for correct syntax.

Marso · Posted: Thu Mar 31, 2011 3:44 pm

in SDSF, place yourself on these lines:

Victor Niu · Posted: Fri Apr 01, 2011 11:55 am

PeterHolland · Posted: Fri Apr 01, 2011 1:46 pm

Victor,

thanks for the reply. Did you follow Enrico's suggestion? And if yes,
what was the result?

By the way i still have nightmares about working with TBSM.

Victor Niu · Posted: Wed Apr 06, 2011 2:12 pm

thanks so much for enrico,expat,Kjeld,PeterHolland,Marso.You are always warm-hearted，your suggestion gave me lots of inspiration.

Now this problem resolved by rexx endit and sub jcl:

/*REXX*/
JOBNAME='TBSM01.PLEXG1.DB2.SOURCE(TBSMDB3J)'
CHGDB='AAAAAAAA'
CHGDATE='BBBBBBBBBB'
CHATIME='CCCCCCCCCC'
DB2_NAME='ALERTSTA'
DB2_ROATEA='2011-07-01'
DB2_ROATEB='-23.59.59.999999'
CALL SUBJOB JOBNAME DB2_NAME DB2_ROATEA DB2_ROATEB

EXIT
SUBJOB:
ARG JOBNM DBNAME ROTDATE ROTTIME
SAY JOBNM DBNAME ROTDATE ROTTIME
"NEWSTACK"
QUEUE "//ROTAEDIT JOB ,CLASS=M,REGION=4M,NOTIFY=&SYSUID, "
QUEUE "// MSGLEVEL=(1,1),MSGCLASS=X "
QUEUE "//CPAEDIT EXEC PGM=IKJEFT01,DYNAMNBR=60 "
QUEUE "//SYSTSPRT DD SYSOUT=* "
QUEUE "//SYSTSIN DD * "
QUEUE " EDIT '"||JOBNM||"' TEXT NONUM "
QUEUE " CHANGE * 999 '"||CHGDB||"' '"||DBNAME||"' ALL "
QUEUE " CHANGE * 999 '"||CHGDATE||"' '"||ROTDATE||"' ALL "
QUEUE " CHANGE * 999 '"||CHATIME||"' '"||ROTTIME||"' ALL "
QUEUE " SUBMIT
QUEUE "/*
QUEUE "$"
SAY "QEND"
"SUBMIT * END($)"
"DELSTACK"
RETURN