IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Change NULL indicator in UNLOAD utility


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Khadhar Basha

New User


Joined: 28 Oct 2014
Posts: 44
Location: India

PostPosted: Fri Dec 15, 2017 3:01 pm
Reply with quote

Hi Experts,

I am in process of doing a POC of z/Os DB2 to DB2 LUW 11.1 migration.

I ran UNLOAD and the SYSPUNCH looks like below

Code:
LOAD DATA INDDN SYSREC   LOG NO  RESUME YES
 EBCDIC  CCSID(00037,65534,65534)
 INTO TABLE
 "HEXM002".
 "DEPARTMENT"
 NUMRECS                    3
 ( "DEPTNO"
  POSITION(  00001:00003) CHAR(00003)
 , "DEPTNAME"
  POSITION(  00004:00034) VARCHAR
 , "MGRNO"
  POSITION(  00036:00041) CHAR(00006)
                          NULLIF(00035)=X'FF'
 , "ADMRDEPT"
  POSITION(  00042:00044) CHAR(00003)
 , "LOCATION"
  POSITION(  00046:00061) CHAR(00016)
                          NULLIF(00045)=X'FF'
 )



Now I have problem specifying NULL indicator while uploading data into LUW.

In LUW the load utility syntax as follows :
Code:

load from D:\db2V11Express\load\department.txt of asc method l modified by striptblank nullindchar=x'FF' (1 3,4 34,36 41,42 44,46 61) null indicators (0,0,35,0,45)
insert into DEPARTMENT;


But as per db2luw 11.1 manuals
Quote:
nullindchar must specify symbols included in the standard ASCII set between code points x20 and x7F


which means I cannot mention x'FF' while loading data into table.

Is there any way to specify values between code points x20 and x7F as null indicator in unloaded data in z/Os unload utility DSNUTILB.

I have an other method as optional of creating a dynamic sort card with the use syspunch output to overlay X'FF' with ASCII set between code points x20 and x7F, but reading and editing all the downloaded code with take much time and cpu.

Please help me out, if there is a way to specify null values indicator other than x'FF' in unloaded file thru utility itself.
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Fri Dec 15, 2017 7:10 pm
Reply with quote

meditate a bit longer on the DB2 LUW manuals ...
EBCDIC vs ASCII , NULL INDICATOR LIST , ..., ..., ...
Back to top
View user's profile Send private message
Khadhar Basha

New User


Joined: 28 Oct 2014
Posts: 44
Location: India

PostPosted: Sat Dec 16, 2017 10:34 am
Reply with quote

Hi Enrico,

Thanks for the replay, When I read LUW load manuals I found below.

Quote:
If there is a Y in the column's null indicator position for a given record, the column will be NULL. If there is an N, the data values in the column's data positions of the input record (as defined in L(........)) are used as the source of column data for the row.


or we can use nullindchar to specify the null indicator
Quote:
nullindchar must specify symbols included in the standard ASCII set between code points x20 and x7F


So, when I unload using DSNUTILB I get X'FF' in my file to specify the columns as NULL or not.

If I want to just upload without doing any changes to my data I need to use nullindchar=x'FF' in my LUW load statement - but nullindchar will not accept X'FF'

otherwise, I need to edit data X'FF' overlay with Y or N or other valid nullindchar between X20 and X7F.

Goal is I need a way to upload data into LUW without having to edit the data.
That is I need to change the null indicator during DSNUTILB itself.

Please correct me if I am missing any other options of nullindcahr from LUW manuals, kindly let me know if there are any other options.

Thanks
Khadhar Basha
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Load new table with Old unload - DB2 DB2 6
No new posts Remote Unload of CLOB Columns DB2 6
No new posts Multiple table unload using INZUTILB DB2 2
No new posts REASON 00D70014 in load utility DB2 6
No new posts changing defaults in db2 admin - Unlo... DB2 0
Search our Forums:

Back to Top