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
 

 

Only data rqrd as a result of SQL query (not formatted one)

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
nkumar

New User


Joined: 09 Mar 2008
Posts: 28
Location: USA

PostPosted: Wed Nov 12, 2008 9:02 pm    Post subject: Only data rqrd as a result of SQL query (not formatted one)
Reply with quote

Hi

I am executing following JCL for which I am getting a formatted result.

Code:
//SPUFI    EXEC PGM=IKJEFT01,DYNAMNBR=100                     
//SYSTSIN  DD *                                               
  DSN SYSTEM(DBD)                                             
RUN PROGRAM(DSNTEP2) PARM('ALIGN(LHS)') PLAN(DSNTEP2) +       
  LIB('NDB2P.DSN.SDSNLOAD')                                   
  END                                                         
/*                                                           
//SYSIN    DD *                                               
     SELECT MASTER_ID, COMANY_CODE                                       
     FROM   V1LFODAN.CONTE                                   
     FETCH FIRST 2 ROWS ONLY;                                 
/*                                                           
//SYSTSPRT DD SYSOUT=*                                       
//SYSPRINT DD SYSOUT=*,OUTLIM=500                             


Result -

Code:
1PAGE    1                                                             
 ***INPUT STATEMENT:                                                   
       SELECT MASTER_ID, COMPANY_CODE                                 
       FROM   V1LFODAN.CONTE                                           
       FETCH FIRST 2 ROWS ONLY;                                       
        +--------------------------------+                             
        |    MASTER_ID    | COMPANY_CODE |                             
        +--------------------------------+                             
      1_|      2307946355 | 023          |                             
      2_|      2307947052 | 023          |                             
        +--------------------------------+                             
                                                                       
0SUCCESSFUL RETRIEVAL OF        2 ROW(S)   



But I need to get result with out any formatting.(Which I will put into file to be used as input in further step.)

Code:
     2307946355  023 
     2307947052  023 


Can anyone please help.
Back to top
View user's profile Send private message

Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2180
Location: @my desk

PostPosted: Wed Nov 12, 2008 9:08 pm    Post subject:
Reply with quote

Nitesh,

Use DSNTIAUL program which unloads the data in the same format as in the table. You'll have to do additional formatting in the SQL to make it in desired format.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2180
Location: @my desk

PostPosted: Wed Nov 12, 2008 9:14 pm    Post subject:
Reply with quote

Nitesh,

Here's a sample job.
Code:
//PS100   EXEC PGM=IKJEFT1A,DYNAMNBR=100   
//*                                             
//SYSREC00  DD DSN=Output.dataset,     
//*                                     
//SYSTSPRT  DD SYSOUT=* 
//SYSPUNCH  DD SYSOUT=*
//SYSPRINT  DD SYSOUT=*
//SYSUDUMP  DD SYSOUT=*
//*                                     
//SYSIN     DD *
  your sql
//SYSTSIN   DD *
  DSN SYSTEM(DBD)                                 
  RUN PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) PARM('SQL') -
  LIB('NDB2P.DSN.SDSNLOAD')                         
  END                                               
/*
Back to top
View user's profile Send private message
nkumar

New User


Joined: 09 Mar 2008
Posts: 28
Location: USA

PostPosted: Thu Nov 13, 2008 10:16 am    Post subject: Reply to: Only data rqrd as a result of SQL query (not forma
Reply with quote

Hi Arun, Thanks for the reply.

I tried to execute the JCL suggested by you but nothing comes in the output dataset(NCDPNXK.SPUFI.RESULT1). I get the result in sysout but that is still formatted.

Here is the jcl i m using now

Code:
//SPUFI    EXEC PGM=IKJEFT1A,DYNAMNBR=100                     
//SYSREC00  DD DSN=NCDPNXK.SPUFI.RESULT1,DISP=SHR             
//SYSTSPRT DD SYSOUT=*                                       
//SYSPUNCH DD SYSOUT=*                                       
//SYSPRINT DD SYSOUT=*                                       
//SYSUDUMP  DD SYSOUT=*                                       
//SYSIN    DD *                                               
     SELECT MASTER_ID,COMPANY_CODE                           
     FROM   V1LFODAN.CONTE                                   
     FETCH FIRST 2 ROWS ONLY;                                 
/*                                                           
//SYSTSIN  DD *                                               
  DSN SYSTEM(DBD)                                             
RUN PROGRAM(DSNTEP2) PARM('ALIGN(LHS)') PLAN(DSNTEP2) +       
  LIB('NDB2P.DSN.SDSNLOAD')                                   
  END                                                         
/*                                                           
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 Nov 13, 2008 10:46 am    Post subject:
Reply with quote

Hello,

Look at Arun's RUN PROGRAM statement, then look at yours. . .
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2180
Location: @my desk

PostPosted: Thu Nov 13, 2008 10:48 am    Post subject:
Reply with quote

Quote:
I tried to execute the JCL suggested by you
Nitesh,

Unfortunately, you did not. You're still executing DSNTEP2 program which produces output in a report format.
Back to top
View user's profile Send private message
nkumar

New User


Joined: 09 Mar 2008
Posts: 28
Location: USA

PostPosted: Thu Nov 13, 2008 10:57 am    Post subject:
Reply with quote

It was my mistake......m sorry.....

Thanks Arun, it worked fine........
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2180
Location: @my desk

PostPosted: Thu Nov 13, 2008 11:07 am    Post subject:
Reply with quote

Quote:
It was my mistake......m sorry.....

Thanks Arun, it worked fine........
Nitesh,

Not to worry. You're 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 -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Join in SQL Query vickey_dw DB2 2 Tue Jan 17, 2017 12:15 am
No new posts Storing huge volume of data, compare ... Pradeep K M All Other Mainframe Topics 3 Mon Jan 16, 2017 5:08 pm
No new posts how to recover an uncataloged VSAM da... archanamuthukrishnan All Other Mainframe Topics 3 Wed Jan 11, 2017 6:18 pm
This topic is locked: you cannot edit posts or make replies. MQ setup query mohitsaini Java & MQSeries 4 Sun Jan 08, 2017 1:53 am
No new posts DB2 query Using Recursion, Converting... smilewithashu2 DB2 1 Tue Jan 03, 2017 12:50 pm


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