Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Wanted to write sql queries in the jcl

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM
View previous topic :: :: View next topic  
Author Message
crrindia

Active User


Joined: 02 Jul 2005
Posts: 124
Location: Gurgaon

PostPosted: Wed Mar 01, 2006 5:30 pm    Post subject: Wanted to write sql queries in the jcl
Reply with quote

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.
Back to top
View user's profile Send private message

priyesh.agrawal

Senior Member


Joined: 28 Mar 2005
Posts: 1452
Location: Chicago, IL

PostPosted: Wed Mar 01, 2006 6:09 pm    Post subject: Re: wanted to write sql queries in the jcl
Reply with quote

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

Code:
//STEPNAME EXEC PGM=IKJEFT01,DYNAMNBR=20           
//STEPLIB  DD DSN=DB2.DSNLOAD.LIBRARY,DISP=SHR   
//SYSTSPRT DD SYSOUT=*                             
//SYSPRINT DD SYSOUT=* 
//SYSTSIN  DD *                                     
  DSN SYSTEM(ENV NAME)                                 
  RUN  PROGRAM(PROG NAME)                             
  END                                               
/*                                                 
//SYSIN  DD DSN=SQL.QUERY.DATASET,
//       DISP=SHR                                   
/*                                                   


Regards,
Priyesh.
Back to top
View user's profile Send private message
ofer71

Global Moderator


Joined: 27 Dec 2005
Posts: 2360
Location: Israel

PostPosted: Wed Mar 01, 2006 6:35 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
i413678
Currently Banned

Active User


Joined: 19 Feb 2005
Posts: 112
Location: chennai

PostPosted: Wed Mar 01, 2006 7:07 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
crrindia

Active User


Joined: 02 Jul 2005
Posts: 124
Location: Gurgaon

PostPosted: Thu Mar 02, 2006 7:34 pm    Post subject:
Reply with quote

Hi Pavan, thank you very much.

Thanks!
crrindia.

i413678 wrote:
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
Back to top
View user's profile Send private message
om

New User


Joined: 02 Mar 2006
Posts: 1
Location: Bangalore

PostPosted: Thu Mar 02, 2006 8:20 pm    Post subject:
Reply with quote

Code:

//stepname  EXEC PGM=IKJEFT01,DYNAMNBR=number                           
//SYSTSPRT DD SYSOUT=*                                             
//SYSTSIN  DD *                                                     
 DSN SYSTEM(sysname)                                                   
 RUN  PROGRAM(DSNTEP2) PLAN(planname)  -                           
       LIB('system lib')                                   
 END                                                               
/*                                                                 
//SYSPRINT DD sysout=*                                             
//SYSIN    DD  DSN=input ps where query is there,DISP=SHR
Back to top
View user's profile Send private message
crrindia

Active User


Joined: 02 Jul 2005
Posts: 124
Location: Gurgaon

PostPosted: Fri Mar 03, 2006 10:42 pm    Post subject: Re: wanted to write sql queries in the jcl
Reply with quote

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.
Back to top
View user's profile Send private message
crrindia

Active User


Joined: 02 Jul 2005
Posts: 124
Location: Gurgaon

PostPosted: Tue Mar 07, 2006 12:23 am    Post subject: Re: wanted to write sql queries in the jcl
Reply with quote

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.
Back to top
View user's profile Send private message
amitava
Warnings : 1

Active User


Joined: 30 Oct 2005
Posts: 186
Location: India

PostPosted: Tue Mar 07, 2006 10:34 am    Post subject:
Reply with quote

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]
Back to top
View user's profile Send private message
crrindia

Active User


Joined: 02 Jul 2005
Posts: 124
Location: Gurgaon

PostPosted: Tue Mar 07, 2006 12:54 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
elansamy

New User


Joined: 07 Dec 2005
Posts: 6

PostPosted: Wed Mar 08, 2006 5:14 pm    Post subject: loading into db2 table thru jcl
Reply with quote

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?
Back to top
View user's profile Send private message
prakash_19

New User


Joined: 27 Nov 2006
Posts: 1
Location: India

PostPosted: Wed Nov 29, 2006 12:54 pm    Post subject: Re: Query on Uload JCL
Reply with quote

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
Back to top
View user's profile Send private message
guptae

Moderator


Joined: 14 Oct 2005
Posts: 1187
Location: Bangalore,India

PostPosted: Wed Nov 29, 2006 12:59 pm    Post subject:
Reply with quote

Hi Prakash,

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



Corrections r welcome
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts How to write Rexx program to size and... sreejeshcs CLIST & REXX 14 Thu Oct 12, 2017 7:26 am
No new posts Match two files using multiple keys a... santoshks1987 SYNCSORT 11 Fri Aug 18, 2017 10:50 am
No new posts Read two inputs and write into output... murali.andaluri DFSORT/ICETOOL 6 Wed Jul 26, 2017 7:35 pm
No new posts Wanted to calculate TRK when i have B... Balu5491 JCL & VSAM 10 Wed Jul 19, 2017 5:43 pm
No new posts Write out NODUPS but just from one file Jay Villaverde DFSORT/ICETOOL 8 Fri Jul 14, 2017 12:44 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us