|
View previous topic :: View next topic
|
| Author |
Message |
shankarm
Active User
Joined: 17 May 2010
Posts: 175
Location: India
|
|
|
|
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 |
|
 |
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
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 |
|
|
Akatsukami
Global Moderator
Joined: 03 Oct 2009
Posts: 1788
Location: Bloomington, IL
|
|
|
|
| DUNS_NBR is evidently defined as numeric (INTEGER, DECIMAL(m,n), etc.) Use the CHAR function to cast it to character. |
|
| Back to top |
|
|
shankarm
Active User
Joined: 17 May 2010
Posts: 175
Location: India
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
Possibly because that is what is in those data positions? |
|
| Back to top |
|
|
shankarm
Active User
Joined: 17 May 2010
Posts: 175
Location: India
|
|
|
|
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 |
|
|
Akatsukami
Global Moderator
Joined: 03 Oct 2009
Posts: 1788
Location: Bloomington, IL
|
|
|
|
| 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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
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 |
|
|
shankarm
Active User
Joined: 17 May 2010
Posts: 175
Location: India
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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 |
|
|
Akatsukami
Global Moderator
Joined: 03 Oct 2009
Posts: 1788
Location: Bloomington, IL
|
|
|
|
| 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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| 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 |
|
|
don.leahy
Active Member
Joined: 06 Jul 2010
Posts: 765
Location: Whitby, ON, Canada
|
|
|
|
| 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 |
|
|
shankarm
Active User
Joined: 17 May 2010
Posts: 175
Location: India
|
|
|
|
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 |
|
|
|
|
