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
 

 

How to determine the record number of a table?

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
Absinthia

New User


Joined: 10 Jan 2008
Posts: 36
Location: CHINA

PostPosted: Mon Oct 20, 2008 8:23 pm    Post subject: How to determine the record number of a table?
Reply with quote

I have two selections to go depending on record number of table A.

If record number is zero ,then go to step1.if not ,go to step2.

However,it is not permitted to unload table A to determine the number because of cpu cost.(table A is a little bigger).

How can I implement it ?
Using SQL [select count(*) from A] seems to work,but i don't know the details in JCL.
Back to top
View user's profile Send private message

superk

Moderator Team Head


Joined: 26 Apr 2004
Posts: 4648
Location: Raleigh, NC, USA

PostPosted: Mon Oct 20, 2008 8:30 pm    Post subject: Reply to: How to determine the record number of a table?
Reply with quote

What does this have to do with either DFSORT or JCL?
Back to top
View user's profile Send private message
Absinthia

New User


Joined: 10 Jan 2008
Posts: 36
Location: CHINA

PostPosted: Mon Oct 20, 2008 8:51 pm    Post subject:
Reply with quote

superk,
I just want to implement the function with JCL. I wonder whether DFSORT can do it.

the result (record number)of select count(*) from A can be only contained in a PS,and the type is hex.
Back to top
View user's profile Send private message
guptae

Moderator


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

PostPosted: Mon Oct 20, 2008 9:16 pm    Post subject:
Reply with quote

Hello Absinthia,


Quote:
If record number is zero ,then go to step1.if not ,go to step2.

However,it is not permitted to unload table A to determine the number because of cpu cost.(table A is a little bigger).

How can I implement it ?
Using SQL [select count(*) from A] seems to work,but i don't know the details in JCL.


Your requirement is not clear & what do you mean by record number?
Could you please put it more clear form to enable us to provide solution.
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Mon Oct 20, 2008 9:26 pm    Post subject:
Reply with quote

absinthia,

step0100 gets the count from the table.
step0200 checks if the count from the table is zero. if it is indeed zero then a return code of 4 is set
step0300 will excuete if the return code of step0200 is zero and if the return code is 4 it will skip it
step0400 will excuete if the return code of step0200 is NOT zero and if the return code is 4 it will execute it


Code:

//STEP0100 EXEC PGM=IKJEFT01         
//SYSTSPRT DD SYSOUT=*,DCB=BLKSIZE=121
//SYSPRINT DD SYSOUT=*               
//SYSTSIN  DD *                       
 DSN SYSTEM(xxxx)                     
 RUN  PROGRAM(DSNTIAUL) -             
      PLAN(DSNTIAUL)    -             
      PARMS('SQL')      -             
      LIB('DB2T.RUNLIB.LOAD')         
//SYSREC00 DD DSN=userid.UNLOAD.count,
//            DISP=(NEW,CATLG,DELETE),
//            UNIT=SYSDA,             
//            SPACE=(TRK,(1,1),RLSE) 
//SYSPUNCH DD SYSOUT=*               
//SYSIN    DD *                       
  SELECT COUNT(*) FROM TABLE;
/*
//STEP0200 EXEC PGM=ICEMAN           
//SYSOUT   DD SYSOUT=*               
//SORTIN   DD DSN=userid.UNLOAD.count,DISP=SHR       
//SORTOUT  DD DUMMY                   
//SYSIN    DD *                       
  OPTION NULLOUT=RC4                 
  INCLUDE COND=(1,4,BI,EQ,0)         
  SORT FIELDS=COPY                   
/*                                   
//STEP0300 EXEC PGM=URPGM,COND=(4,EQ,STEP0200)
...

//STEP0400 EXEC PGM=URPGM,COND=(0,EQ,STEP0200)


Hope this helps...

Cheers
Back to top
View user's profile Send private message
Absinthia

New User


Joined: 10 Jan 2008
Posts: 36
Location: CHINA

PostPosted: Mon Oct 20, 2008 9:33 pm    Post subject:
Reply with quote

Hi,guptae.

Now I want to use step TEST to unload table CM in order to dertermine
whether the table is blank or not . if blank,then go to step NORMAL,or
go to step ABNORM.

However,this method will request cpu cost because the table is not small.

So my question is :is there any other method that can implement the same function
Code:
//*       TEST TABLE CM IF IT IS EMPTY                                */
//TEST    EXEC PGM=IKJEFT01,                                           
//             DYNAMNBR=100                                             
//SYSTSPRT  DD SYSOUT=*                                                 
//SYSTSIN   DD DISP=SHR,DSN=BG04.DS.FIW.DB2UTIL(DSNTIAUL)               
//SYSPRINT  DD SYSOUT=*                                                 
//SYSUDUMP  DD SYSOUT=*                                                 
//SYSREC00  DD DSN=&&TEMP,                                             
//             DISP=(,PASS),                                           
//             UNIT=VTS,LABEL=(,SL,RETPD=010),VOL=(,,,9),               
//             MGMTCLAS=LMONTH,                                         
//             DCB=(RECFM=FB,LRECL=1,BLKSIZE=6000)                     
//SYSPUNCH  DD SYSOUT=*,                                               
//             DCB=(RECFM=FB,LRECL=80)                                 
//SYSIN DD DISP=SHR,DSN=EV604K.BG04.DS.FIW.DB2UTIL(UNLDCM)
//*IF NOT EMPTY,GO TO STEP010,ELSE THEN GO TO STEP015                 */
//STEP1 EXEC PGM=ICETOOL                                               
//TOOLMSG DD SYSOUT=*                                                   
//DFSMSG  DD SYSOUT=*                                                   
//IN      DD DSN=&&TEMP,DISP=(OLD,DELETE)                               
//TOOLIN  DD *                                                         
  COUNT FROM(IN) EMPTY                                                 
//CHK     IF (STEP1.RC = 0 ) THEN                                     
//NORMAL EXEC PGM=IEFBR14                                           
//NEW1   DD   DSN=EV604K.NORMAL.TEST,DISP=(,CATLG),SPACE=(TRK,  (1,1)),DCB=(RECFM=FB,LRECL=80,BLKSIZE=80)                                   
//       ELSE IF STEP1.RC = 12 THEN
//ABNORM EXEC PGM=IEFBR14                                             
//NEW2   DD   DSN=EV604K.ABNORM.TEST,DISP=(,CATLG),SPACE=(TRK,(1,1)),
//         DCB=(RECFM=FB,LRECL=80,BLKSIZE=80)                         
// ENDIF
Back to top
View user's profile Send private message
Absinthia

New User


Joined: 10 Jan 2008
Posts: 36
Location: CHINA

PostPosted: Mon Oct 20, 2008 9:48 pm    Post subject:
Reply with quote

hi,Skolusu
Thanks very much,that is what i wanted. icon_lol.gif

I greatly appreciate your help.

guptae,superk.

thank you all the same. icon_biggrin.gif
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Mon Oct 20, 2008 9:59 pm    Post subject:
Reply with quote

Quote:
Now I want to use step TEST to unload table CM in order to dertermine
whether the table is blank or not


I would suggest to use the following SQL instead of using COUNT(*) if your intention is to just determine if you have atleast one row in the table. This will consume least amount of cpu.

Code:

SELECT * FROM TABLE FETCH FIRST 1 ROW ONLY;


and change step0200 sysin control cards to the following

Code:

/SYSIN    DD *                 
  OPTION NULLOUT=RC4             
  SORT FIELDS=COPY               
/*



Hope this helps...

Cheers
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 -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Adding big TEXT lines to each record ... bshkris SYNCSORT 4 Sat May 06, 2017 1:40 am
No new posts SMTP the current generation number of... Jyothi Kulunde JCL & VSAM 4 Thu May 04, 2017 4:08 pm
This topic is locked: you cannot edit posts or make replies. Merge two files and update Trailer re... Yashashri JCL & VSAM 1 Thu May 04, 2017 12:54 pm
No new posts unload data from table with lob columns farhad_evan DB2 0 Sat Apr 22, 2017 1:32 pm
No new posts Sort Large record length cmsmoon DFSORT/ICETOOL 14 Tue Apr 11, 2017 5:49 pm


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