IBM Mainframe Forum

by **danylele74** » Wed Jul 09, 2014 5:15 pm

Hello,
i need to execute a Select into a JCL.
I have a parameter into a file.
The file has N records and the parameter is at position 600.
For each record i need to execute the select.
If the Select returns +100 (not found) and must execute another SELECT.
If the second Select returns +100 (not found), i must to replace a DATA field (of the same record) as 01.01.0001 (dd.mm.yyyy) at position X.
How can i do?
Thank you.

by **enrico-sorichetti** » Wed Jul 09, 2014 7:23 pm

unfortunately You will have to write a program
the logic is too complex to be implemented thru a plain utility.

by **danylele74** » Wed Jul 09, 2014 7:48 pm

And do only this steps?

- The file has N records and the parameter is at position 600.
- For each record i need to execute the select.

Is it possible?

by **enrico-sorichetti** » Wed Jul 09, 2014 10:17 pm

YES ...
it will be a two step approach
1) build the SELECT statements in the appropriate form for the DB2/SQL utility You are going to use
2) run the chosen DB2/SQL utility providing as input the file built in step 1

NOT a JCL problem ...
JCL in se does absolutely nothing but prepare the environment for the program You need to run

by **danylele74** » Wed Jul 09, 2014 10:50 pm

Enrico, maybe you are italian. If yes, please try to explain to me in italian via mp.
Thanks

by **enrico-sorichetti** » Thu Jul 10, 2014 1:40 am

Yes I am Italian, but a forum is not for P2P help
answers are for the benefit of the community also.

see here on how to build some arbitrary sequence of statements from some input
syncsort-synctool/topic9973.html?hilit=build

( obviously the content of the output depends on the program used for the select )

by **danylele74** » Thu Jul 10, 2014 12:57 pm

Maybe I did not understand your answer but I do not think I need a dynamic sort.
Or rather, with a dynamic sort can I create a file to two files as input?

by **danylele74** » Thu Jul 10, 2014 2:16 pm

Hi,
I'm trying to execute this step in my jcl

Select all

//STEP080 EXEC PGM=IKJEFT01,DYNAMNBR=20
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSPUNCH DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(TDB2)
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) PARM('SQL')
//SYSREC00 DD DSN=FILEOUT.TABLE,
// DISP=(NEW,CATLG,DELETE),
// UNIT=SYSDA,SPACE=(CYL,(15,20),RLSE),
// DCB=(LRECL=80,BLKSIZE=800,RECFM=FB)
//SYSIN DD *
SELECT *
FROM XGS003.TBDELAFF ;

but I receive an ERROR that I don't understand.

Select all

1 DSNT490I SAMPLE DATA UNLOAD PROGRAM
0 DSNT505I DSNTIAUL OPTIONS USED: SQL
0 DSNT503I UNLOAD DATA SET SYSPUNCH RECORD LENGTH SET TO 80
0 DSNT504I UNLOAD DATA SET SYSPUNCH BLOCK SIZE SET TO 80
0 DSNT570I WARNING: THIS APPLICATION HAS SELECTED DSNTIAR TO PERFORM SQL
0 MESSAGE FORMATTING BECAUSE THE GET DIAGNOSTICS SERVICE RETURNED
0 AN UNEXPECTED SQLCODE WHILE FORMATTING MESSAGES FOR ONE OR MORE
0 SQL WARNING OR ERROR CONDITIONS. A DUMP OF THE SQLCA FOR THE GET
0 DIAGNOSTICS REQUEST FOLLOWS:

0 SQLCODE : -818 SQLSTATE: 51003 SQLERRP : DSNXEPM
0 SQLERRMC: 192478E90BE46514.1911BB7018163E07.DSNTIAUL
0 SQLERRD1: -200 SQLERRD2: 0 SQLERRD3: 0
0 SQLERRD4: -1 SQLERRD5: 0 SQLERRD6: 0
0 SQLWARN0: 1: 2: 3: 4: 5: 6: 7: 8: 9: A:
DSNT408I SQLCODE = -818, ERROR: THE PRECOMPILER-GENERATED TIMESTAMP 192478E90B
FROM THE BIND TIMESTAMP 1911BB7018163E07 BUILT FROM THE DBRM DSNTIAUL
DSNT418I SQLSTATE = 51003 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXEPM SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = -200 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'FFFFFF38' X'00000000' X'00000000' X'FFFFFFFF' X'000
INFORMATION

Can you help me? Thanks

IBM Mainframe Forum

select with parameters from file

select with parameters from file

Re: select with parameters from file

Re: select with parameters from file

Re: select with parameters from file

Re: select with parameters from file

Re: select with parameters from file

Re: select with parameters from file

Re: select with parameters from file