Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Write an INSERT to a table using a control card in JCL

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

New User


Joined: 01 Feb 2007
Posts: 30
Location: Mumbai

PostPosted: Thu Feb 14, 2008 9:20 am    Post subject: Write an INSERT to a table using a control card in JCL
Reply with quote

I have a scenario wherein I need to write an INSERT to a table using a control card in JCL.The values to be inserted into the table columns may vary so i want to pass them through the job card.How do i do it.

Please help.


Thanks in advance
Back to top
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Feb 14, 2008 9:25 am    Post subject:
Reply with quote

Hello,

You cannot directly issue SQL commands in JCL.

Values cannot be passed on the JOB statement. . .

I'd suggest you talk with someone in your project and see which utility/method is used for this kind of requirement on your system.
Back to top
View user's profile Send private message
tushar_study

New User


Joined: 01 Feb 2007
Posts: 30
Location: Mumbai

PostPosted: Thu Feb 14, 2008 9:34 am    Post subject:
Reply with quote

I am sorry that I goofed up my xplaination.I meant that the SQL will receive the value to inserted throug the PROC.The SQL will be written in the SYSIN DD *(Equivalent to a control card).Please help me with a short example.



Regards
Back to top
View user's profile Send private message
acevedo

Active User


Joined: 11 May 2005
Posts: 344
Location: Spain

PostPosted: Thu Feb 14, 2008 12:52 pm    Post subject:
Reply with quote

search for SPUFI in batch.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Feb 14, 2008 8:39 pm    Post subject:
Reply with quote

Hello,

Did you find some batch SPUFI example(s)?

Quote:
The SQL will be written in the SYSIN DD *(Equivalent to a control card).Please help me with a short example.
I suspect that your batch SPUFI execution will use a SYSIN DD DSN= rather than a DD * (if you have a PROCedure for your process.

Once you have your jcl and control statements, post them here if there are questions.
Back to top
View user's profile Send private message
tushar_study

New User


Joined: 01 Feb 2007
Posts: 30
Location: Mumbai

PostPosted: Thu Feb 14, 2008 11:39 pm    Post subject: Please suggest an alternative
Reply with quote

Hi ,

I tried a SELECT in my control card

Select * from MYTABLE
where SOME_DATE=&PARM1;

I was passing the PARM1 from my Job.It shows an error msg that PARM1
not used in PROC.Please suggest an alternative.


Regards
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Thu Feb 14, 2008 11:47 pm    Post subject: Re: Please suggest an alternative
Reply with quote

tushar_study wrote:
Hi ,

I tried a SELECT in my control card

Select * from MYTABLE
where SOME_DATE=&PARM1;

I was passing the PARM1 from my Job.It shows an error msg that PARM1
not used in PROC.Please suggest an alternative.


Regards


Control cards and other such cards are not JCL and there is no symbolic substitution for them.
Back to top
View user's profile Send private message
Phrzby Phil

Active Member


Joined: 31 Oct 2006
Posts: 962
Location: Richmond, Virginia

PostPosted: Fri Feb 15, 2008 12:00 am    Post subject:
Reply with quote

Try this, but it's a bit involved:

1. set up a file with your SQL as a template, with place-holding names like YYYY-MM-DD
2. write a program (SAS, COBOL, etc.) to take a PARM populated in JCL with your symbolic and copy the template to another file (e.g., &&SQL), replacing YYYY-MM-DD with the parm
3. pass &&SQL to the SPUFI's SYSIN
Back to top
View user's profile Send private message
tushar_study

New User


Joined: 01 Feb 2007
Posts: 30
Location: Mumbai

PostPosted: Fri Feb 15, 2008 1:25 am    Post subject:
Reply with quote

Hi Folks,

Thats great .I will write a Cobol program
Back to top
View user's profile Send private message
lenin

New User


Joined: 12 Oct 2007
Posts: 11
Location: Chennai

PostPosted: Mon Feb 18, 2008 6:21 pm    Post subject:
Reply with quote

HI tushar,

I could partly help for your requirement.

1. I agree we can not pass a parm value inside a JCL control card. We have search for some thing else.

2.But we can execute SQL as Control card though a JCL. The pgm is IKJEFT01

pls find the below piece of JCL.

//STEP01 EXEC PGM=IKJEFT01
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//LISTING DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSPUNCH DD DUMMY
//SYSREC00 DD DSN=<Output PS>,DISP=SHR
//SYSTSIN DD *
DSN SYSTEM(DBBG)
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIBSP) -
LIB('SYSD.DBBG.RUNLIB.LOAD')
END
/*

//SYSIN DD DSN=<Query stored in PS or PDS>,DISP=SHR


Thanks,
Lenin
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 SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
This topic is locked: you cannot edit posts or make replies. How to use 2 input files in control c... Gunapala CN DFSORT/ICETOOL 23 Thu Oct 13, 2016 3:42 pm
No new posts Error IEC161I 052(009,XXXRS00)-084 wh... amitc23 JCL & VSAM 10 Wed Oct 12, 2016 5:00 pm
No new posts Need Rexx Program to Load in to DB2 T... Hareesh Verma CLIST & REXX 22 Tue Sep 20, 2016 2:59 pm
This topic is locked: you cannot edit posts or make replies. Sort card to replace text using cross... Susanta SYNCSORT 32 Tue Sep 13, 2016 4:17 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us