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
 
load and delete table through JCL with parmdata

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

Active User


Joined: 21 Aug 2008
Posts: 107
Location: hyd

PostPosted: Thu Feb 01, 2018 12:47 am    Post subject: load and delete table through JCL with parmdata
Reply with quote

HI Friends,

There is parm-file which have first letter as F or space.

using this i need to:

1) if F , I need to delete records in the table(using query) or load the table with empty file.

2) if spaces. then i need to load a Db2 table using the input file provided in the respective step.

Can you please suggest how to do with thiS through JCL.

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

ram_vizag

Active User


Joined: 21 Aug 2008
Posts: 107
Location: hyd

PostPosted: Thu Feb 01, 2018 1:16 am    Post subject: Re: load and delete table through JCL with parmdata
Reply with quote

ram_vizag wrote:
HI Friends,

There is parm-file which have first letter as F or space.

using this i need to:

1) if F , I need to delete records in the table(using query) or load the table with empty file.

2) if spaces. then i need to load a Db2 table using the input file provided in the respective step.

Can you please suggest how to do with thiS through JCL.

Thanks
Ram



Actually parm file has 26 records and in 26th record first letter will be F or spaces.

one way which I'm trying to do is:

1) In first step I'll check if if record in the Parm file is having F , then i'll create a new file which will have a record with F
2) In second step i'll use If condition if a new file is created then i'll load table with empty file.(will use IEBR14 utility)
3) Else(if the new is not created) I'll load the table with the given file.

is my approach correct?
Back to top
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8685
Location: Back in jolly old England

PostPosted: Thu Feb 01, 2018 12:38 pm    Post subject:
Reply with quote

Your requirement is not that clear ............

Are you talking about one table or multiple tables with each parm record relating to one table.

If one table, when F is specified, do you remove ALL rows from said table or only specific rows. If this is for only one table and all rows are deleted, why bother loading it to delete it later.

You need to put your question in simple but concise terms so that it is easily understood by those that may wish to help you. As I have said in a previous post, psychic day was Wednesday this week.
Back to top
View user's profile Send private message
Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1284
Location: Israel

PostPosted: Thu Feb 01, 2018 2:46 pm    Post subject:
Reply with quote

Clear as mud... icon_sad.gif


Step 1: you run SYNCSORT/DFSORT or SYNCTOOL/ICETOOL and finish the step with a return code of 0 if there is a space and return code of 4 if there is an F (or not a space).

Step 2: if step1 return code is 0, execute this step which will load the database

Step 3: if step1 return code is 4, execute this step which will purge your DB2 table with IEFBR14...
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2020
Location: UK

PostPosted: Thu Feb 01, 2018 5:54 pm    Post subject:
Reply with quote

How do you expect to load a table using IEFBR14? DB/2 tables are handled by DB/2. IEFBR14 is not DB/2 - it is a program that does nothing (except, possibly, set an EOF marker on data sets supplied via DD statements).
Back to top
View user's profile Send private message
ram_vizag

Active User


Joined: 21 Aug 2008
Posts: 107
Location: hyd

PostPosted: Thu Feb 01, 2018 9:13 pm    Post subject:
Reply with quote

Marso wrote:
Clear as mud... icon_sad.gif


Step 1: you run SYNCSORT/DFSORT or SYNCTOOL/ICETOOL and finish the step with a return code of 0 if there is a space and return code of 4 if there is an F (or not a space).

Step 2: if step1 return code is 0, execute this step which will load the database

Step 3: if step1 return code is 4, execute this step which will purge your DB2 table with IEFBR14...


Thanks a lot for you guidance.

I already did this yesterday as below:

step1: checks if parm file have ONLY F at position 1 in the record and copies that particular record into a new file creating(say xxxx.yyyy) in this step.

step2: checks if the new file (xxxx.yyyy) is created or not using listcat

IF RC= 0

STEP3(PERFORMS DELETE)

ELSE

STEP4(PERFORMS LOADING)

ENDIF.
Back to top
View user's profile Send private message
Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1284
Location: Israel

PostPosted: Thu Feb 01, 2018 9:59 pm    Post subject:
Reply with quote

If you are using a Sort product for step1, consider using parameter NULLOUT
Code:
//STEP01   EXEC  PGM=SORT,PARM='NULLOUT=RC4'
If nothing written to SORTOUT then finish with RC=4 (otherwise finish with RC=0)
Then you don't need Step2.

Quote:
delete records in the table(using query)
If that is possible, then that is too:
I wrote:
purge your DB2 table with IEFBR14...
Or not ? icon_wink.gif
Back to top
View user's profile Send private message
ram_vizag

Active User


Joined: 21 Aug 2008
Posts: 107
Location: hyd

PostPosted: Fri Feb 02, 2018 12:46 am    Post subject:
Reply with quote

Marso wrote:
If you are using a Sort product for step1, consider using parameter NULLOUT
Code:
//STEP01   EXEC  PGM=SORT,PARM='NULLOUT=RC4'
If nothing written to SORTOUT then finish with RC=4 (otherwise finish with RC=0)
Then you don't need Step2.

Quote:
delete records in the table(using query)
If that is possible, then that is too:
I wrote:
purge your DB2 table with IEFBR14...
Or not ? icon_wink.gif


Thanks a lot Marso.

I am thinking to reduce more steps in the JCL.

May I know if there is any way for:

1) If Record(s) in PARMFILE has value 'F' in the 5:1 position, Can I set RC=0 and RC=4 for any other value?

the sort card for this is:

Code:
SORT FIELDS=COPY             
INCLUDE COND=(5,1,CH,EQ,C'F')



if so i need not create a file and avoid doing LISTCAT too( which reduces one step)

as I have RC values now, I can use them for load or delete....
Back to top
View user's profile Send private message
ram_vizag

Active User


Joined: 21 Aug 2008
Posts: 107
Location: hyd

PostPosted: Fri Feb 02, 2018 1:06 am    Post subject:
Reply with quote

ram_vizag wrote:
Marso wrote:
If you are using a Sort product for step1, consider using parameter NULLOUT
Code:
//STEP01   EXEC  PGM=SORT,PARM='NULLOUT=RC4'
If nothing written to SORTOUT then finish with RC=4 (otherwise finish with RC=0)
Then you don't need Step2.

Quote:
delete records in the table(using query)
If that is possible, then that is too:
I wrote:
purge your DB2 table with IEFBR14...
Or not ? icon_wink.gif


Thanks a lot Marso.

I am thinking to reduce more steps in the JCL.

May I know if there is any way for:

1) If Record(s) in PARMFILE has value 'F' in the 5:1 position, Can I set RC=0 and RC=4 for any other value?

the sort card for this is:

Code:
SORT FIELDS=COPY             
INCLUDE COND=(5,1,CH,EQ,C'F')



if so i need not create a file and avoid doing LISTCAT too( which reduces one step)

as I have RC values now, I can use them for load or delete....



I did this and removed step.. Thanks a lot.

However I have one more query(not related this requirement which I'm working...)

How can I set 3 or more different RC for values:

1) If 5:1 position is having F then set RC=0
2) If S then RC=4
3) If E then RC=8

Can you please let me know.

4) can I code in such a way that if the value has M then file should be written or the file should not be written at all....
Back to top
View user's profile Send private message
sergeyken

Active User


Joined: 29 Apr 2008
Posts: 309
Location: Maryland

PostPosted: Fri Feb 02, 2018 3:33 am    Post subject:
Reply with quote

Nic Clouston wrote:
IEFBR14 is not DB/2 - it is a program that does nothing (except, possibly, set an EOF marker on data sets supplied via DD statements).

It doesn't.
Code:
IEFBR14 CSECT
        SR  15,15      set RC=0
        BR  14         return to caller
        END IEFBR14

- all the rest of real code is irrelevant.

EOF is being added by allocation program handling DISP=NEW, after some improvements provided by IBM long time ago. In ancient times (but I've had a chance to investigate this) any DISP=NEW did not create any EOF; this might cause unpredictable results in the following steps, that's why IBM had to fix it.
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 Need help on TDQ load from file using... subratarec CICS 4 Wed Jun 13, 2018 11:32 pm
No new posts How to delete large data. Mounika Nemani DB2 1 Wed Jun 13, 2018 1:58 pm
No new posts Extracting DDL for all list of intere... Ashishpanpaliya DB2 5 Mon May 21, 2018 4:08 pm
No new posts Searching a table for the field name? socker_dad COBOL Programming 8 Sat Mar 31, 2018 2:57 am
No new posts Need inputs on DB2 tablespace/table r... ashek15 DB2 3 Sat Mar 24, 2018 11:49 pm

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