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
 

 

Db2 unload table

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

New User


Joined: 22 Jan 2008
Posts: 85
Location: India

PostPosted: Tue Jul 14, 2009 9:49 am    Post subject: Db2 unload table
Reply with quote

Hi,
Im trying to unload a db2 table in a readable format, but im getting some
junk values, im defining my field in the jcl, but still some of my columns are not displayed,
Here is my jcl,
Code:

//U162936X  JOB KGCACCT#,                                               
//             U162936,       **JOB STATEMENT GENERATED BY SUBMIT**     
//             NOTIFY=U162936,                                         
//             MSGLEVEL=(1,1)                                           
//UTIL EXEC DSNUPROC,SYSTEM=DSN1,UTPROC=''                             
//DSNUPROC.SYSREC DD DSN=U162936.IN.PS,                                 
//     DISP=SHR                                                         
//DSNUPROC.SYSPUNCH DD SYSOUT=*                                         
//DSNUPROC.SYSIN    DD  *                                               
UNLOAD TABLESPACE DB086301.TS001 FROM TABLE U162936.SUPPLIER1           
( S_NO  INTEGER EXTERNAL,                                               
S_NAME  CHAR,                                                           
S_STAT  SMALLINT,                                                       
S_CITY  CHAR)                                                           
/*                                                                     


My dclgen for the table is
Code:


My output is,

***********************************************************
    EXEC SQL DECLARE SUPPLIER1 TABLE                       
    ( S_NO                           INTEGER,             
      S_NAME                         CHAR(20) NOT NULL,   
      S_STAT                         SMALLINT,             
      S_CITY                         CHAR(15)             
    ) END-EXEC.                                           
***********************************************************


Code:

****** ***************************** Top of Data *********************
000001  1 2          31420C712             H NEWYORK                 
000002  1 4          31420C712             À QUEENLN                 
000003  1 5          31420C713             À CHENNAI                 
000004  1 6          31420C713             H CALIFORNIA               
000005  1 2          31420C712             H NEWYORK                 
000006  1 4          1748E492              À QUEENLN                 
000007  1 6          1748E492              H CHENNAI                 
000008  1 7          1748E492                IRVINE                   
****** **************************** Bottom of Data *******************
                                                                     


Please help me to unload the table in readable format with correct positioning .
Back to top
View user's profile Send private message

Bharath Bhat

Active User


Joined: 20 Mar 2008
Posts: 283
Location: chennai

PostPosted: Tue Jul 14, 2009 10:30 am    Post subject:
Reply with quote

Whenever the table has nullable columns, DB2 will prefix a null indicator to indicate if the value in that column is null. Please look in the SYSPUNCH dataset to find out the correct format of the data that you unloaded.
Most of the cases, null indicator is a smallint preceeding the nullable columns in the unload dataset. You need to add the null indicators to your dclgen so that the unload dataset could be read.
Back to top
View user's profile Send private message
usharaniA

New User


Joined: 22 Jan 2008
Posts: 85
Location: India

PostPosted: Tue Jul 14, 2009 3:56 pm    Post subject:
Reply with quote

what do you mean by null column,
i have data in all the column specefied but while unloading the datas are not displayed.
Back to top
View user's profile Send private message
Bharath Bhat

Active User


Joined: 20 Mar 2008
Posts: 283
Location: chennai

PostPosted: Tue Jul 14, 2009 5:52 pm    Post subject:
Reply with quote

If you do not specify NOT NULL for any column, that would become nullable.

Quote:
Please look in the SYSPUNCH dataset to find out the correct format of the data that you unloaded.
Back to top
View user's profile Send private message
usharaniA

New User


Joined: 22 Jan 2008
Posts: 85
Location: India

PostPosted: Tue Jul 14, 2009 5:57 pm    Post subject:
Reply with quote

But my question here is i have data in all the four column that i have specefied , but the third column (S_STAT) is not displayed in the output.

Is there any JCL which unloads the table with correct positioning , i mean the data should start from column one,
here in the output the valid datas start from the column 4.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6967
Location: porcelain throne

PostPosted: Tue Jul 14, 2009 6:07 pm    Post subject:
Reply with quote

Quote:
but the third column (S_STAT) is not displayed in the output.

i imagine that it is, otherwise you would have had a failure (rc <> 0).
S_STAT is a small integer - you have not asked for any conversion, it is being dumped as small integer. look at your output with HEX Mode ON.
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


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

PostPosted: Tue Jul 14, 2009 6:17 pm    Post subject:
Reply with quote

usharaniA wrote:
But my question here is i have data in all the four column that i have specefied , but the third column (S_STAT) is not displayed in the output.

Is there any JCL which unloads the table with correct positioning , i mean the data should start from column one,
here in the output the valid datas start from the column 4.


RTFM!
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 unload data from table with lob columns farhad_evan DB2 0 Sat Apr 22, 2017 1:32 pm
This topic is locked: you cannot edit posts or make replies. Need help in estimating space of unlo... ashek15 IMS DB/DC 12 Fri Apr 07, 2017 5:11 am
No new posts Data replication from multiple Db2 ta... kishpra DB2 9 Mon Mar 27, 2017 9:58 pm
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 Join of more than one table dibyendumandal11111 DB2 6 Mon Feb 13, 2017 11:36 pm


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