data unload

Ron Thomas

data unload

Hi. When i am doing a unload i am getting the null indicator field also in the file.Could someone let me know how to eliminate the same . Attached is the screenshot of the same


UNLOAD TABLESPACE
      DB2 YES
      LOCK NO
      QUIESCE NO
      SELECT
             CHAR('|'),
             DIGITS(IEM_NBR),
             CHAR('|'),
             COALESCE(DIGITS(SERVICE_CODE),'     '),
             CHAR('|'),
             DIGITS(EANX_PACK_TYPE_CODE),
             CHAR('|'),
             DIGITS(PURCHS_PACK_TYPE_CODE),
             CHAR('|'),
--
--
FROM MX_N1E_ITEWM_CPLMT
FORMAT  DSNTIAUL
OUTDDN  (UNLOAD)
LOADDDN (PUNCH)
LOADOPT (LOG NO  ENFORCE NO  INDD SYSREC00
         SORTDEVT SYSDA  SORTNUM 32)

Attachments

  • Capture.PNG (23.1k)
Edited By:
Ron Thomas[Organization Members] @ Feb 07, 2019 - 08:00 AM (America/Eastern)

Roy Boxwell

data unload
(in response to Ron Thomas)
You need to give a CHAR and lentgh to all of the columns you are uusing otherwise it will default to varchar. Eg for the column



COALESCE(DIGITS(SERVICE_CODE),' '),





You should use



CHAR(COALESCE(DIGITS(SERVICE_CODE),CHAR(' ', 5) , 5),



Do the same for any other cols and your length codes should vanish...





Roy Boxwell

SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: <mailto:[login to unmask email]> [login to unmask email]
Web http://www.seg.de http://www.seg.de

https://www.seg.de/corporate/rechtliche-hinweise/datenschutz Link zur Datenschutzerklärung


Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Ulf Heinrich



From: Ron Thomas [mailto:[login to unmask email]
Sent: Thursday, February 7, 2019 1:55 PM
To: [login to unmask email]
Subject: [DB2-L] - data unload



Hi. When i am doing a unload i am getting the length field also in the file.Could someone let me know how to eliminate the same . Attached is the screenshot of the same


UNLOAD TABLESPACE
DB2 YES
LOCK NO
QUIESCE NO
SELECT
CHAR('|'),
DIGITS(IEM_NBR),
CHAR('|'),
COALESCE(DIGITS(SERVICE_CODE),' '),
CHAR('|'),
DIGITS(EANX_PACK_TYPE_CODE),
CHAR('|'),
DIGITS(PURCHS_PACK_TYPE_CODE),
CHAR('|'),
--
--
FROM MX_N1E_ITEWM_CPLMT
FORMAT DSNTIAUL
OUTDDN (UNLOAD)
LOADDDN (PUNCH)
LOADOPT (LOG NO ENFORCE NO INDD SYSREC00
SORTDEVT SYSDA SORTNUM 32)



-----End Original Message-----

Attachments

  • smime.p7s (5.1k)

Ron Thomas

RE: data unload
(in response to Roy Boxwell)

Roy - when i used that here is what i am getting

 



-INZU532I BIF_COMPATIBILITY SETTINGS : CURRENT FOR NATIVE MODE AND V9_DECIMAL_VARCHAR FOR SQL MODE
          AS A RESULT OF THE SETTING FOR BIF_COMPATIBILITY AND COMPATIBILITTY_FROM_DB2_SETTINGS
          IN VUU030/ULOPTNS PARMLIB PARAMETER AND DB2 BIF_COMPATIBILITY SETTINGS
 INZU010I ITEM STARTING AT (10,14) ENDING AT (10,23)
0INZU016E SELECT STATEMENT SYNTAX IS INVALID
0INZU039E FATAL SYNTAX ERROR BEFORE LINE 60 COLUMN 73
 INZU366I UTILITY RETURN CODE 8 (REASON CODE 0x107a002)

 

SELECT
       CHAR('|'),
       DIGITS(ITEM_NBR),
       CHAR('|'),
       CHAR(COALESCE(DIGITS(SERVICE_CODE),CHAR(' ', 5),5),
       CHAR('|'),

Roy Boxwell

data unload
(in response to Ron Thomas)
Typed too fast:



CHAR(COALESCE(DIGITS(SERVICE_CODE),CHAR(' ', 5) , 5))



Last bracket was missing!



Roy Boxwell

SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: <mailto:[login to unmask email]> [login to unmask email]
Web http://www.seg.de http://www.seg.de

https://www.seg.de/corporate/rechtliche-hinweise/datenschutz Link zur Datenschutzerklärung


Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Ulf Heinrich



From: Ron Thomas [mailto:[login to unmask email]
Sent: Thursday, February 7, 2019 3:08 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: data unload



Roy - when i used that here is what i am getting





-INZU532I BIF_COMPATIBILITY SETTINGS : CURRENT FOR NATIVE MODE AND V9_DECIMAL_VARCHAR FOR SQL MODE
AS A RESULT OF THE SETTING FOR BIF_COMPATIBILITY AND COMPATIBILITTY_FROM_DB2_SETTINGS
IN VUU030/ULOPTNS PARMLIB PARAMETER AND DB2 BIF_COMPATIBILITY SETTINGS
INZU010I ITEM STARTING AT (10,14) ENDING AT (10,23)
0INZU016E SELECT STATEMENT SYNTAX IS INVALID
0INZU039E FATAL SYNTAX ERROR BEFORE LINE 60 COLUMN 73
INZU366I UTILITY RETURN CODE 8 (REASON CODE 0x107a002)



SELECT
CHAR('|'),
DIGITS(ITEM_NBR),
CHAR('|'),
CHAR(COALESCE(DIGITS(SERVICE_CODE),CHAR(' ', 5),5),
CHAR('|'),



-----End Original Message-----

Attachments

  • smime.p7s (5.1k)

Ron Thomas

RE: data unload
(in response to Roy Boxwell)

Still i am getting error. Error details attached. The column datatype is smallint

Attachments

  • Capture.PNG (13.8k)
Edited By:
Ron Thomas[Organization Members] @ Feb 07, 2019 - 09:59 AM (America/Eastern)

Ron Thomas

RE: data unload
(in response to Ron Thomas)

Roy - I changed to this CAST(COALESCE(DIGITS(ACQ_SERVICE_CODE),CHAR(' ', 5)) AS CHAR) and it worked . Thanks for the help