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 Write R1 and R2 to OP1 and R1 to OP2 bhaskar_kanteti DFSORT/ICETOOL 10 Fri Mar 03, 2017 11:18 am
No new posts how to send just 10 rows in a CICS sc... Megha Gupta CICS 5 Thu Feb 23, 2017 6:57 pm
No new posts Can you write 2 recs on SORTOUT when ... Sysaron DFSORT/ICETOOL 6 Thu Feb 23, 2017 12:07 am
No new posts Join of more than one table dibyendumandal11111 DB2 6 Mon Feb 13, 2017 11:36 pm
No new posts Sort Card to Remove Duplicate records... raj4neo SYNCSORT 2 Wed Jan 25, 2017 4:44 am


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