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 utility

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

Active User


Joined: 17 May 2010
Posts: 175
Location: India

PostPosted: Mon Sep 24, 2012 11:39 pm    Post subject: DB2 unload utility
Reply with quote

All,

Am using the below code to unload data from a db2 table.
Code:

 //COGSMUL JOB ('3000-000000-07-Z-00000000000'),'DB2 COMPLIE',
 //  MSGCLASS=H,CLASS=1,NOTIFY=COGSM                         
 //STEP01 EXEC PGM=IKJEFT01,DYNAMNBR=20                       
 //SYSTSPRT DD SYSOUT=*                                       
 //SYSTSIN DD *                                               
  DSN SYSTEM(PRD7)                                           
  RUN PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) PARMS('SQL') -         
  LIB('SYS1.SYSB.DB2LOAD')                                   
 //SYSPRINT DD  SYSOUT=*                                     
 //SYSPUNCH DD  SYSOUT=*                                     
 //SYSREC00 DD  DSN=TRD.TSS.TEST.UNLOAD,                     
 //         DISP=(NEW,CATLG,DELETE),                         
 //         SPACE=(CYL,(105,10),RLSE)                         
 //SYSIN    DD  *                                             
      SELECT DUNS_NBR,PG_LINK_ADR FROM DBA.ERTPG_LINKS;       
 //*                                                         


I am getting the alphanumeric output correct but the numbers are not coming correct. spaces became low values.

Below is the sample data,
Code:

........www.kent-mfg.com........................................................
...äi...www.lawsonproducts.com..................................................
...ê.*..www.rwtownandcountry.com................................................
........www.consolidatedthreadmills.com.........................................


How can i correct this? what am i doing wrong?
Please help.
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


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

PostPosted: Tue Sep 25, 2012 12:03 am    Post subject:
Reply with quote

DUNS_NBR is either integer or decimal - my guess integer
CAST as CHAR

PG_LINK_ADR is probably VARCHAR,
SUBSTR(PG_LINK_ADR,1,LENGTH(PG_LINK_ADR))
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1738
Location: Bloomington, IL

PostPosted: Tue Sep 25, 2012 12:03 am    Post subject:
Reply with quote

DUNS_NBR is evidently defined as numeric (INTEGER, DECIMAL(m,n), etc.) Use the CHAR function to cast it to character.
Back to top
View user's profile Send private message
shankarm

Active User


Joined: 17 May 2010
Posts: 175
Location: India

PostPosted: Tue Sep 25, 2012 12:29 am    Post subject:
Reply with quote

Thanks guys, i used the cast function. the number is displayed correctly. but still i have low values after the characters.

example below,
Code:

00000602002...www.kent-mfg.com..........................
00000543890...www.lawsonproducts.com....................
00000052295...www.rwtownandcountry.com..................
00001000330...www.consolidatedthreadmills.com...........
00001001478...www.nationalnonwovens.com.................


All the trailing '.....' are low values.
I tried rtrim option also, its not working.

Code:

SELECT CHAR(DUNS_NBR),                                 
       RTRIM(SUBSTR(PG_LINK_ADR,1,LENGTH(PG_LINK_ADR)))
        FROM DBA.ERTPG_LINKS;                         
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: Tue Sep 25, 2012 12:41 am    Post subject:
Reply with quote

Hello,

Possibly because that is what is in those data positions?
Back to top
View user's profile Send private message
shankarm

Active User


Joined: 17 May 2010
Posts: 175
Location: India

PostPosted: Tue Sep 25, 2012 12:54 am    Post subject:
Reply with quote

ya you are correct thats what i believe.
I guess i have to write a syncsort to replace all low values with spaces??

I can search for x'00' and replace x'40' using ss option correct?
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1738
Location: Bloomington, IL

PostPosted: Tue Sep 25, 2012 12:59 am    Post subject:
Reply with quote

shankarm wrote:
Thanks guys, i used the cast function. the number is displayed correctly. but still i have low values after the characters.

example below,
Code:

00000602002...www.kent-mfg.com..........................
00000543890...www.lawsonproducts.com....................
00000052295...www.rwtownandcountry.com..................
00001000330...www.consolidatedthreadmills.com...........
00001001478...www.nationalnonwovens.com.................


All the trailing '.....' are low values.
I tried rtrim option also, its not working.

Code:

SELECT CHAR(DUNS_NBR),                                 
       RTRIM(SUBSTR(PG_LINK_ADR,1,LENGTH(PG_LINK_ADR)))
        FROM DBA.ERTPG_LINKS;                         

As Mr. Scherrer suggests, PGM_LINK_ADR may be a CHAR column that is actually padded with X'00' rather than spaces. Try:
Code:
SELECT CHAR(DUNS_NBR),                                 
       TRANSLATE(PG_LINK_ADR,' ',X'00')
        FROM DBA.ERTPG_LINKS;       

(Incidentally, to minimize future thrashing about, it would be well to find out how these columns are defined by querying SYSIBM.SYSCOLUMNS.)
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: Tue Sep 25, 2012 1:00 am    Post subject:
Reply with quote

Hello,

Be careful as there will probably be some x'00' values that are valid (i.e. the high order part of a packed-decimal or binary field).
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Tue Sep 25, 2012 1:04 am    Post subject:
Reply with quote

change this:
RTRIM(SUBSTR(PG_LINK_ADR,1,LENGTH(PG_LINK_ADR)))

to this:
RTRIM(REPLACE(SUBSTR(PG_LINK_ADR,1,LENGTH(PG_LINK_ADR),X'00',X'40)))
Back to top
View user's profile Send private message
shankarm

Active User


Joined: 17 May 2010
Posts: 175
Location: India

PostPosted: Tue Sep 25, 2012 1:10 am    Post subject:
Reply with quote

Akatsukami,

I tried this, its not working..

TRANSLATE(PG_LINK_ADR,' ',X'00')
TRANSLATE(PG_LINK_ADR,'00 ',X'40')
TRANSLATE(PG_LINK_ADR,'40',X'00')

All the above not working...
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: Tue Sep 25, 2012 1:32 am    Post subject:
Reply with quote

Hello,

Forum Help 101 - Do Not post "it didn't work". Just a waste of space / time.

What happened when you tried these variations?
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1738
Location: Bloomington, IL

PostPosted: Tue Sep 25, 2012 1:42 am    Post subject:
Reply with quote

shankarm wrote:
Akatsukami,

I tried this, its not working..

TRANSLATE(PG_LINK_ADR,' ',X'00')
TRANSLATE(PG_LINK_ADR,'00 ',X'40')
TRANSLATE(PG_LINK_ADR,'40',X'00')

All the above not working...

Well. And the query on SYSIBM.SYSCOLUMNS showed you...?
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Tue Sep 25, 2012 2:59 am    Post subject:
Reply with quote

Code:
SELECT HEX(PG_LINK_ADR)
  FROM DBA.ERTPG_LINKS
 FETCH FIRST 10 ROWS ONLY;   

Code:
SELECT NAME, TBNAME, COLTYPE, LENGTH, NULLS, DEFAULT
       FROM SYSIBM.SYSCOLUMNS
       WHERE TBNAME='ERTPG_LINKS'
       ORDER BY 2,1;

link to above sql
Back to top
View user's profile Send private message
don.leahy

Active Member


Joined: 06 Jul 2010
Posts: 641
Location: Whitby, ON, Canada

PostPosted: Tue Sep 25, 2012 7:05 pm    Post subject:
Reply with quote

Have you tried CHAR(PG_LINK_ADR,n) Where n = the maximum width of the column? The CHAR function should pad the field with blanks out to a length of n.
Back to top
View user's profile Send private message
shankarm

Active User


Joined: 17 May 2010
Posts: 175
Location: India

PostPosted: Wed Sep 26, 2012 3:31 pm    Post subject:
Reply with quote

Thanks all of you for your help.
I spoke to the user, the user say its ok if the file has trailing low values. So, No issues as of now.

i didn't try don.leahy's and Dick Brenholtz's solution yet. i will try this and post it in the forum for future use. Thanks.
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 Best IMS DB Unload Utility jjabez10 IMS DB/DC 12 Thu Sep 22, 2016 11:15 pm
No new posts Any docs / links for isub- cobol comp... SRICOBSAS COBOL Programming 4 Fri Aug 26, 2016 2:35 pm
No new posts DB2 Unload format options Susanta DB2 2 Fri Aug 12, 2016 5:42 pm
No new posts Comparex utility to Syncsort conversion mistah kurtz SYNCSORT 11 Fri Jul 22, 2016 1:53 pm
No new posts DXT unload utility - DVRE0000 mistah kurtz DB2 1 Thu Jul 07, 2016 4:56 pm


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