Wanted to write sql queries in the jcl

crrindia · Posted: Wed Mar 01, 2006 5:30 pm

Hi I wanted to write sql queries in the jcl to retrieve the data from the table of the database and insert into another database of the table. For that purpose I am not aware of coding these kind of things. Anyone of you please explain about it.

Thanks!
CRRIndia.

priyesh.agrawal · Posted: Wed Mar 01, 2006 6:09 pm

You can use IKJEFT01 to run SQL queries from a Batch job.

ofer71 · Posted: Wed Mar 01, 2006 6:35 pm

I think the poster wanted to code his queries directly in the JCL, not by running a program.

To perform queries directly, you can use IBM's DSNTEP2 and DSNTIAD.

O.

i413678 · Posted: Wed Mar 01, 2006 7:07 pm

Hi,

this can be done using the following lines of code

in which SYSTSIN you have to mention the controlcards like below and in SYSIN you have to mentione the query to execute..............

Here in JCL there are two datasets

1.DSN=TSOPPOL.EXTRACT.INACCUST.PUNC----the fields are stored in this dataset
2.DSN=TSOPPOL.EXTRACT.INACCUST.DATA----the actual data will be stored after executing the sql query...........

//R010 EXEC PGM=IKJEFT01
//STEPLIB DD DSN=ENDEVOR.DEV1.LOAD,
// DISP=SHR
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSPUNCH DD DSN=TSOPPOL.EXTRACT.INACCUST.PUNC,
// DISP=SHR
//SYSREC00 DD DSN=TSOPPOL.EXTRACT.INACCUST.DATA,
// DISP=SHR
//SYSTSIN DD *
DSN SYSTEM (TDB2)
RUN PROGRAM (DSNTIAUL) PLAN(DSNTIAUL) -
PARM('SQL') LIB ('TDB2.RUNLIB.LOAD')
END
/*
//SYSIN DD *
SELECT B.OFF_ID, B.XACCTNUM, A.TOTL_FEE_AM
FROM EBVT_PND_BILL_EVNT A,
EBET_PND_BILL_NTRY B
WHERE A.ENT_ID = B.ENT_ID
AND A.EVNT_TY_CD = 'IAMX'
AND A.CHRG_BPRL_CD = '013'
AND A.CHRG_CD = '1'
/*

I hope u understood...................

pavan

crrindia · Posted: Thu Mar 02, 2006 7:34 pm

Hi Pavan, thank you very much.

Thanks!
crrindia.

om · New User Joined: 02 Mar 2006 Posts: 1 Location: Bangalore

crrindia · Posted: Fri Mar 03, 2006 10:42 pm

Hi Once again I am thanking all of you for your efforts on my quote.
Now I wanted to know some thing about the Control Cards. and what is the purpose of these control cards, which we are writing in our above mentioned jcl. and what are all the other uses of Control Cards. It would be helpful for me if any one of you give the earliest reply.

Thanks!
CrrIndia.

crrindia · Posted: Tue Mar 07, 2006 12:23 am

Hi All, now I wanted to copy the data from control cards to the database using sql query in the jcl. Please help me any one of you. Its an urgent.
Thanks!
CrrIndia.

amitava · Active User Joined: 30 Oct 2005 Posts: 186 Location: India

Hey crrindia,
Look according to ur requirement u want to load the data in ur database. Am I correct?
If it is so, then I am sending u the JCL -
If you want to obtain the LOAD utility control statements for loading
rows into a table, but you do not want to unload the rows, you can set
the data set names for the SYSRECnn data sets to DUMMY. For example,
to obtain the utility control statements for loading rows into the
department table, you invoke DSNTIAUL like this:

[code]
//UNLOAD EXEC PGM=IKJEFT01,DYNAMNBR=20
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DSN)
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIB71) -
LIB('DSN710.RUNLIB.LOAD')
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSREC00 DD DUMMY
//SYSPUNCH DD DSN=DSN8UNLD.SYSPUNCH,
// UNIT=SYSDA,SPACE=(800,(15,15)),DISP=(,CATLG),
// VOL=SER=SCR03,RECFM=FB,LRECL=120,BLKSIZE=1200
//SYSIN DD *
DSN8710.DEPT
[\code]

crrindia · Posted: Tue Mar 07, 2006 12:54 pm

Hi Amitava, thank you very much for your immediate response. I already unloaded from one database of the table
into the Syspunch, and Sysrec00 by using the the Unload sql.Now I wanted to load these data into another
database of the table(tables are same in both the databases).

My unload jcl is follows.

SQL UNLOAD ENVIRONMENT FROM STAGE

000056 //UNLD001 EXEC PGM=IKJEFT01,
000057 // DYNAMNBR=20
000058 //STEPLIB DD DSN=DB2.T.DBZ0.SDSNEXIT,
000059 // DISP=SHR
000060 // DD DSN=DB2.T.DBZ0.SDSNLOAD,
000061 // DISP=SHR
000062 //SYSTSPRT DD SYSOUT=*
000063 //SYSTSIN DD *
000064 DSN SYSTEM(DGZ0)
000065 RUN PROGRAM(DSNTIAUL) PLAN(DSNTIB41) PARMS('SQL') -
000066 LIB('DB2.T.DBZ0.RUNLIB.LOAD')
000067 /*
000068 //SYSPRINT DD SYSOUT=*
000069 //SYSUDUMP DD SYSOUT=*
000070 //SYSPUNCH DD DSN=BAS&CLIENT..P0.CEENVR.SQL.V,
000071 // DISP=(NEW,CATLG),
000072 // UNIT=SYSDA,
000073 // SPACE=(CYL,(1,1),RLSE)
000074 //SYSREC00 DD DSN=BAS&CLIENT..P0.CEENVR.DATA.V,
000075 // DISP=(NEW,CATLG),
000076 // UNIT=SYSDA,
000077 // SPACE=(CYL,(100,50),RLSE)
000078 //SYSIN DD *
000079 SELECT
000080 CASE
000081 WHEN CLNT_ID = '0CMPA' THEN
000082 SUBSTR(SUBSTR(CLNT_ID,2,4) CONCAT ENVR_NM,1,15)
000083 ELSE SUBSTR(CLNT_ID CONCAT ENVR_NM,1,15)
000084 END
000085 , CHAR('19000101')
000086 , SUBSTR(DIGITS(PRVS_NMBR_ID),4,7)
000087 , ENVR_DATA_TX
000088 FROM AAAAAA.CE_ENVR_%%CL
000089 WHERE ENVR_NM = '%%EN%%.'
000090 AND ENVR_TS = (SELECT MAX(ENVR_TS) AS MAX_TS
000091 FROM AAAAAA.CE_ENVR_%%CL
000092 WHERE ENVR_NM = '%%EN%%.'
000093 AND PRVS_NMBR_ID = 1)
000094 ORDER BY PRVS_NMBR_ID DESC;
000095 /*
000096 //*

Please give me the jcl for load into another database.

Thanks!
CrrInida.

elansamy · New User Joined: 07 Dec 2005 Posts: 6

My JCL is like this:

//STEPNAME EXEC PGM=IKJEFT01,DYNAMNBR=1
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM (DB2D)
RUN PROGRAM (DSNTEP2) PLAN(DSNTEP2) -
PARM('SQL') LIB('SYSM.DB2.DB2D.SDSNEXIT')
END
/*
//SYSIN DD DSN=AVE4234.LOADJCL.DATASET,DISP=SHR

Inside the dataset AVE4234.LOADJCL.DATASET I doded the below query.

INSERT INTO NW1YE.TBL_CNTL
(CNTL_ID,CNTL_TMST)
VALUES
('PYEDD341',CURRENT TIMESTAMP);

when i tried to run the job, i am getting the error message:

DSNT499I ERROR IN INPUT PARAMETER LIST

Can anyone help me in fixing this bug?

prakash_19 · New User Joined: 27 Nov 2006 Posts: 1 Location: India

Hi all,

I would like to unload 6 differern DB2 tables. Is it possible to do it in a single UNLOAD JCL?

To specify my query more clearly, Can I use 6 SYSREC00 like SYSREC01 thru SYSREC06 and 6 SYSPUNCH like SYSPNCH1 thru SYSPNCH6 and mentioning SELECT * from all those 6 tables separating them by commas?
Pls respond imm !!!

Regards
Prakash A

guptae · Posted: Wed Nov 29, 2006 12:59 pm

Hi Prakash,

I dont think so.
U have to put six different step for this each for unload one table.

Corrections r welcome