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
 
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: 6968
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 Update table without cursor Ron Klop DB2 3 Wed Oct 11, 2017 1:06 pm
No new posts Table(Unicode(Graphic) table) loading... muralikrishnan_new DB2 0 Thu Oct 05, 2017 5:10 pm
No new posts Insert a Row_number into table useit DB2 2 Tue Sep 19, 2017 1:07 pm
No new posts how to see when the last read access ... Mike 1304 DB2 1 Tue Sep 12, 2017 7:52 pm
No new posts PC (UTF-8) -> z/OS (EBCDIC) -> ... prino All Other Mainframe Topics 4 Fri Sep 01, 2017 1:47 am

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