Unable to unload data with CHAR column in WHEN condition

Bharath Nunepalli

Unable to unload data with CHAR column in WHEN condition

I'm trying to unload data from a table with a CHAR column in the WHEN condition. I used DSNUPROC and DSNUTILB.

None of them are unloading data from the table when I use a CHAR column in WHEN condition. I'm getting 0 rows.

 

Data is getting unloaded when I use INTEGER or other data types in the same table but not for CHAR columns.

These are my JCLs

//UNLD0001 EXEC PGM=DSNUTILB,REGION=0M,
// PARM='SSID,XXX12345,'
//STEPLIB DD DSN=XXXXX.SDSNEXIT,DISP=SHR
// DD DSN=DSNXXXX.SDSNLOAD,DISP=SHR
//SYSPRINT DD SYSOUT=*
//UTPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSIN DD *
UNLOAD TABLESPACE DBNAME.TSNAME
PUNCHDDN SYSPUNCH
UNLDDN SYSREC00
FROM TABLE DBNAME.TBNAME
WHEN (APPROVAL_FLAG = 'Y')
//SYSPUNCH DD DUMMY
//SYSREC00 DD DUMMY
//*


//UNLD1 EXEC DSNUPROC,SYSTEM=SSID,UID='XXX12345'
//STEPLIB DD DSN=XXXXX.SDSNEXIT,DISP=SHR
// DD DSN=DSNXXXX.SDSNLOAD,DISP=SHR
//SYSPRINT DD SYSOUT=*
//UTPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSIN DD *
UNLOAD TABLESPACE DBNAME.TSNAME
FROM TABLE
DBNAME.TBNAME
WHEN (APPROVAL_FLAG = 'Y')
UNLDDN SYSREC00
//SYSPUNCH DD DUMMY
//SYSREC00 DD DUMMY
//*

 

Can someone please help me understand why I'm getting 0 rows when I use CHAR column in WHEN condition even if there is data in the table matching the WHEN condition?

 

 

 

Bharath Nunepalli,

Senior DB2 DBA.

Roy Boxwell

Unable to unload data with CHAR column in WHEN condition
(in response to Bharath Nunepalli)
Is your schema name really the same as your database name?

Roy Boxwell
SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-
Vagedesstrasse 19
40479 Dusseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]
http://www.seg.de
Link zur Datenschutzerklärung https://www.seg.de/corporate/rechtliche-hinweise/datenschutz

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

On 12 Feb 2020, at 17:19, Bharath Nunepalli <[login to unmask email]> wrote:



I'm trying to unload data from a table with a CHAR column in the WHEN condition. I used DSNUPROC and DSNUTILB.

None of them are unloading data from the table when I use a CHAR column in WHEN condition. I'm getting 0 rows.



Data is getting unloaded when I use INTEGER or other data types in the same table but not for CHAR columns.

These are my JCLs

//UNLD0001 EXEC PGM=DSNUTILB,REGION=0M,
// PARM='SSID,XXX12345,'
//STEPLIB DD DSN=XXXXX.SDSNEXIT,DISP=SHR
// DD DSN=DSNXXXX.SDSNLOAD,DISP=SHR
//SYSPRINT DD SYSOUT=*
//UTPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSIN DD *
UNLOAD TABLESPACE DBNAME.TSNAME
PUNCHDDN SYSPUNCH
UNLDDN SYSREC00
FROM TABLE DBNAME.TBNAME
WHEN (APPROVAL_FLAG = 'Y')
//SYSPUNCH DD DUMMY
//SYSREC00 DD DUMMY
//*

//UNLD1 EXEC DSNUPROC,SYSTEM=SSID,UID='XXX12345'
//STEPLIB DD DSN=XXXXX.SDSNEXIT,DISP=SHR
// DD DSN=DSNXXXX.SDSNLOAD,DISP=SHR
//SYSPRINT DD SYSOUT=*
//UTPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSIN DD *
UNLOAD TABLESPACE DBNAME.TSNAME
FROM TABLE
DBNAME.TBNAME
WHEN (APPROVAL_FLAG = 'Y')
UNLDDN SYSREC00
//SYSPUNCH DD DUMMY
//SYSREC00 DD DUMMY
//*



Can someone please help me understand why I'm getting 0 rows when I use CHAR column in WHEN condition even if there is data in the table matching the WHEN condition?







Bharath Nunepalli,

Senior DB2 DBA.

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

Bharath Nunepalli

RE: Unable to unload data with CHAR column in WHEN condition
(in response to Roy Boxwell)

Yes, schema and database name are same.

 

Bharath Nunepalli,

Senior DB2 DBA.

Michael Hannan

RE: Unable to unload data with CHAR column in WHEN condition
(in response to Bharath Nunepalli)

Bharath,

I don't see what is wrong easily. I assume output to dummy should still count how many rows. Try replacing constant value with hex X'E8' in WHEN clause, just in case any encode problems (ASCII, Unicode etc.).

Verify correct count using SQL:

SELECT COUNT(*)   FROM DBNAME.TBNAME
WHERE APPROVAL_FLAG = X'E8' WITH UR;

Get it working as a DSNTIAUL (SQL driven) extract first might be a good idea, then convert to Unload Utility if performance costs demand it.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Feb 13, 2020 - 02:11 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Feb 13, 2020 - 02:11 AM (Europe/Berlin)

Bharath Nunepalli

RE: Unable to unload data with CHAR column in WHEN condition
(in response to Michael Hannan)

This syntax didn't work either. I still got 0 rows.

UNLOAD TABLESPACE DBNAME.TSNAME
PUNCHDDN SYSPUNCH
UNLDDN SYSREC00
FROM TABLE DBNAME.TBNAME
WHEN (APPROVAL_FLAG = X'E8')

 

Unload worked with DSNTIAUL.
I want to understand why unload is not working for CHAR columns in DSNUPROC and DSNUTILB.


Bharath Nunepalli,

Senior DB2 DBA.

James Campbell

RE: Unable to unload data with CHAR column in WHEN condition
(in response to Bharath Nunepalli)

What is the encoding scheme of the table?  If it is ASCII, you'll need to use X'59'.

James Campbell

Bharath Nunepalli

RE: Unable to unload data with CHAR column in WHEN condition
(in response to James Campbell)

I checked the ENCODING_SCHEME and found that its ASCII.
Unload worked after I change it to (APPROVAL_FLAG=X'59')

 

In this particular case, CHAR column length is just 1. So, its easy to convert to hex.
In case of long CHAR columns this will be difficult.

Is the recommendation is DSNTIAUL? Or, is there any other way to make this work with DSNUPROC or DSNUTILB?


Bharath Nunepalli,

Senior DB2 DBA.

Michael Hannan

RE: Unable to unload data with CHAR column in WHEN condition
(in response to Bharath Nunepalli)

Bharath,

Yes its easier (hard to say better) to use DSNTIAUL for many extracts, if the CPU cost and runtime is not an issue. It understands the value 'Y' regardless of the encoding scheme, where the Utility is fussy.

Nice to be getting help from James again. We worked at same shop many years ago, more than once.

If your extract cost is an issue, then can consider the utility, or trying for parallelism with zIIP offload in the access path (SET DEGREE='ANY'), or 60% zIIP offload in Distserv if SQL run remotely via TCPIP, or for extracts of large numbers of rows repeatedly, look at extracting a Delta change file to be combined with previous Baseline extract using ICETOOL to make the +1 Baseline. Some of the more complex options for really costly extracts, however for many extracts, we might be running off peak and cost is not an issue.  It depends!

So in the end, use the Utility if you have a need. Being an SQL person, I often like to use DSNTIAUL (when performance is acceptable).

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Feb 13, 2020 - 09:18 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Feb 13, 2020 - 09:20 AM (Europe/Berlin)

Joe Geller

RE: Unable to unload data with CHAR column in WHEN condition
(in response to Bharath Nunepalli)

A longer string is not really more difficult.  Just do a Select hex('Long Literal') from sysibm.sysdummy1;

Then do a copy and paste from the result to your Unload WHEN(x'  ')

Joe

In Reply to Bharath Nunepalli:

I checked the ENCODING_SCHEME and found that its ASCII.
Unload worked after I change it to (APPROVAL_FLAG=X'59')

 

In this particular case, CHAR column length is just 1. So, its easy to convert to hex.
In case of long CHAR columns this will be difficult.

Is the recommendation is DSNTIAUL? Or, is there any other way to make this work with DSNUPROC or DSNUTILB?

 

Bharath Nunepalli,

Senior DB2 DBA.

Philip Sevetson

Unable to unload data with CHAR column in WHEN condition
(in response to Joe Geller)
Joe,
That doesn’t come back as ASCII, does it? I’d think that query would return EBCDIC.

-phil


Philip Sevetson
Computer Systems Manager
FISA-OPA
5 Manhattan West
New York, NY 10001
[login to unmask email]<mailto:[login to unmask email]>
212-857-1688 w
917-991-7052 m
212-857-1659 f
[cid:[login to unmask email]

From: Joe Geller <[login to unmask email]>
Sent: Thursday, February 13, 2020 9:30 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Unable to unload data with CHAR column in WHEN condition


A longer string is not really more difficult. Just do a Select hex('Long Literal') from sysibm.sysdummy1;

Then do a copy and paste from the result to your Unload WHEN(x' ')

Joe

In Reply to Bharath Nunepalli:

I checked the ENCODING_SCHEME and found that its ASCII.
Unload worked after I change it to (APPROVAL_FLAG=X'59')



In this particular case, CHAR column length is just 1. So, its easy to convert to hex.
In case of long CHAR columns this will be difficult.

Is the recommendation is DSNTIAUL? Or, is there any other way to make this work with DSNUPROC or DSNUTILB?



Bharath Nunepalli,

Senior DB2 DBA.

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
Attachments

  • image001.png (3.3k)

Joe Geller

RE: Unable to unload data with CHAR column in WHEN condition
(in response to Philip Sevetson)

Phil,

Of course you are right.  I meant to do the select from an ASCII table, which would be SYSDUMMYA.  (I tested on a LUW system which was UTF8.

Joe

In Reply to Philip Sevetson:

Joe,
That doesn’t come back as ASCII, does it? I’d think that query would return EBCDIC.

-phil


Philip Sevetson
Computer Systems Manager
FISA-OPA
5 Manhattan West
New York, NY 10001
[login to unmask email]<mailto:[login to unmask email]>
212-857-1688 w
917-991-7052 m
212-857-1659 f
[cid:[login to unmask email]

From: Joe Geller <[login to unmask email]>
Sent: Thursday, February 13, 2020 9:30 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Unable to unload data with CHAR column in WHEN condition


A longer string is not really more difficult. Just do a Select hex('Long Literal') from sysibm.sysdummy1;

Then do a copy and paste from the result to your Unload WHEN(x' ')

Joe

In Reply to Bharath Nunepalli:

I checked the ENCODING_SCHEME and found that its ASCII.
Unload worked after I change it to (APPROVAL_FLAG=X'59')



In this particular case, CHAR column length is just 1. So, its easy to convert to hex.
In case of long CHAR columns this will be difficult.

Is the recommendation is DSNTIAUL? Or, is there any other way to make this work with DSNUPROC or DSNUTILB?



Bharath Nunepalli,

Senior DB2 DBA.

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

Philip Sevetson

Unable to unload data with CHAR column in WHEN condition
(in response to Joe Geller)
Okay, THAT’s cool.

1> SELECT HEX('THE QUICK BROWN FOX JUMPS OVER THE LAZY DOG') FROM SYSIBM.SYSDUMMY1
2> go
1
--------------------------------------------------------------------------------------
E3C8C540D8E4C9C3D240C2D9D6E6D540C6D6E740D1E4D4D7E240D6E5C5D940E3C8C540D3C1E9E840C4D6C7

1> SELECT HEX('THE QUICK BROWN FOX JUMPS OVER THE LAZY DOG') FROM SYSIBM.SYSDUMMYA
2> go
1
--------------------------------------------------------------------------------------
54484520515549434B2042524F574E20464F58204A554D5053204F56455220544845204C415A5920444F47
1>


Philip Sevetson
Computer Systems Manager
FISA-OPA
5 Manhattan West
New York, NY 10001
[login to unmask email]<mailto:[login to unmask email]>
212-857-1688 w
917-991-7052 m
212-857-1659 f
[cid:[login to unmask email]

From: Joe Geller <[login to unmask email]>
Sent: Thursday, February 13, 2020 10:00 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Unable to unload data with CHAR column in WHEN condition


Phil,

Of course you are right. I meant to do the select from an ASCII table, which would be SYSDUMMYA. (I tested on a LUW system which was UTF8.

Joe

In Reply to Philip Sevetson:
Joe,
That doesn’t come back as ASCII, does it? I’d think that query would return EBCDIC.

-phil


Philip Sevetson
Computer Systems Manager
FISA-OPA
5 Manhattan West
New York, NY 10001
[login to unmask email]<mailto:[login to unmask email]><mailto:[login to unmask email]%3e>
212-857-1688 w
917-991-7052 m
212-857-1659 f
[cid:[login to unmask email]

From: Joe Geller
Sent: Thursday, February 13, 2020 9:30 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Unable to unload data with CHAR column in WHEN condition


A longer string is not really more difficult. Just do a Select hex('Long Literal') from sysibm.sysdummy1;

Then do a copy and paste from the result to your Unload WHEN(x' ')

Joe

In Reply to Bharath Nunepalli:

I checked the ENCODING_SCHEME and found that its ASCII.
Unload worked after I change it to (APPROVAL_FLAG=X'59')



In this particular case, CHAR column length is just 1. So, its easy to convert to hex.
In case of long CHAR columns this will be difficult.

Is the recommendation is DSNTIAUL? Or, is there any other way to make this work with DSNUPROC or DSNUTILB?



Bharath Nunepalli,

Senior DB2 DBA.

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
Attachments

  • image001.png (3.3k)