select with parameters from file



JES, JES2, JCL utilities, IDCAMS, Compile & Run JCLs, PROCs etc...

select with parameters from file

Postby 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.
danylele74
 
Posts: 12
Joined: Thu Jul 03, 2014 2:00 am
Has thanked: 0 time
Been thanked: 0 time

Re: select with parameters from file

Postby 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.
cheers
enrico
When I tell somebody to RTFM or STFW I usually have the page open in another tab/window of my browser,
so that I am sure that the information requested can be reached with a very small effort
enrico-sorichetti
Global moderator
 
Posts: 2994
Joined: Fri Apr 18, 2008 11:25 pm
Has thanked: 0 time
Been thanked: 164 times

Re: select with parameters from file

Postby 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?
danylele74
 
Posts: 12
Joined: Thu Jul 03, 2014 2:00 am
Has thanked: 0 time
Been thanked: 0 time

Re: select with parameters from file

Postby 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
cheers
enrico
When I tell somebody to RTFM or STFW I usually have the page open in another tab/window of my browser,
so that I am sure that the information requested can be reached with a very small effort
enrico-sorichetti
Global moderator
 
Posts: 2994
Joined: Fri Apr 18, 2008 11:25 pm
Has thanked: 0 time
Been thanked: 164 times

Re: select with parameters from file

Postby 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
danylele74
 
Posts: 12
Joined: Thu Jul 03, 2014 2:00 am
Has thanked: 0 time
Been thanked: 0 time

Re: select with parameters from file

Postby 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 )
cheers
enrico
When I tell somebody to RTFM or STFW I usually have the page open in another tab/window of my browser,
so that I am sure that the information requested can be reached with a very small effort
enrico-sorichetti
Global moderator
 
Posts: 2994
Joined: Fri Apr 18, 2008 11:25 pm
Has thanked: 0 time
Been thanked: 164 times

Re: select with parameters from file

Postby 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?
danylele74
 
Posts: 12
Joined: Thu Jul 03, 2014 2:00 am
Has thanked: 0 time
Been thanked: 0 time

Re: select with parameters from file

Postby danylele74 » Thu Jul 10, 2014 2:16 pm

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

//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.

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
danylele74
 
Posts: 12
Joined: Thu Jul 03, 2014 2:00 am
Has thanked: 0 time
Been thanked: 0 time


Return to JCL

 


  • Related topics
    Replies
    Views
    Last post