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
 

 

special characters db2 unload

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

New User


Joined: 29 Jun 2014
Posts: 16
Location: India

PostPosted: Thu Jun 25, 2015 12:21 am    Post subject: special characters db2 unload
Reply with quote

I am doing data comparsion using mainframe SUPERC as part of data migration from ADABAS to DB2. I use below JCL step to get all data from DB2 TABLE and then compare the data with ADABAS data. I am most of the time able to get data and with little formatting able to compare data.

But this table is throwing up some special chars like "....." which do not appear in ADABAS. I dont see them in DB2 table its only after I unload the data using below step I see them.

DB2 Unload JCL Step

Code:

//*************************************************************
//STEP01A  EXEC DB2TEST,MOD=DSNTIAUS,     
//            SYSTEM=TEST,USERSYS=DSNX   
//SYSPUNCH DD DSN=TTTTTPR.P998.DBCNT.PUNCH,SPACE=(TRK,(1,1)), 
//            DISP=(NEW,DELETE,DELETE)                         
//SYSREC00 DD DSN=TTTTTPR.PARENT.DB2.TABLE,                   
//             DCB=(RECFM=FB,LRECL=135,BLKSIZE=0),             
//            SPACE=(CYL,(5,5),RLSE),DISP=(MOD,KEEP,KEEP)   
//SYSIN    DD *                                               
SELECT                                                     
       CHAR(CODE_NUMBER),                                 
       CHAR(CODE),                                         
       CHAR(EXPANSION),                                   
       CHAR(NAME),                                         
       CHAR(NUMBER_CODE),                                 
       CHAR(TBL_NAME),                                     
       CHAR(NUMBER_NAME)                                   
  FROM DDDDDDD.TABLE_DATA ;                               
/*       
 

DB2 Unload Output

Code:

    TTTTTPR.PARENT.DB2.TABLE                   Line 00000000 Col 059 13
   ==>                                                  Scroll ===> CSR
    -7----+----8----+----9----+----0----+----1----+----2----+----3----+-
    *********************** Top of Data ********************************
            ..............?    BENA2          B       ..................
            A             .    199805         1A      .    A           
            A             .    199805         1A      .    A           
            B             .    199903         1B      .    B           
            ..............?    970A2          9       ..................
            FAFAFAFAFAFA  .    980A2          9YYYYYY.    FAFAFAFAFAFA
            FAFAFAFAFAFA  .    980A2          9YYYYYY.    FAFAFAFAFAFA
            FAFAFAFAFAFA  .    980A2          9YYYYYY.    FAFAFAFAFAFA
            FAFAFAFAFAFA  .    980A2          9YYYYYY.    FAFAFAFAFAFA
            LLLLLL        .    981A2          9LLLLLL .    LLLLLL   

ADABAS Unload output

Code:


-------------------------------------------------------------------
   BROWSE    TTTTTPR.PARENT.ADABAS.TABLE                Line 00000000
   Command ===>                                                  Scrol
  -7----+----8----+----9----+----0----+----1----+----2----+----3----+-
  ********************************* Top of Data **********************
                            BENA2          B                         .
          A                 199805         1A          A             .
          A                 199805         1A          A             .
          B                 199903         1B          B             .
                            970A2          9                         .
          FAFAFAFAFAFA      980A2          9YYYYYY    FAFAFAFAFAFA  .
          FAFAFAFAFAFA      980A2          9YYYYYY    FAFAFAFAFAFA  .
          FAFAFAFAFAFA      980A2          9YYYYYY    FAFAFAFAFAFA  .
          FAFAFAFAFAFA      980A2          9YYYYYY    FAFAFAFAFAFA  .
          LLLLLL                  981A2          9LLLLLL     LLLLLL        .

What I want to do? I want to get rid of '..............? ' from DB2 unload file and replace them with blank , just like we have blank in ADABAS file.
Back to top
View user's profile Send private message

RahulG31

Active User


Joined: 20 Dec 2014
Posts: 331
Location: USA

PostPosted: Thu Jun 25, 2015 12:36 am    Post subject: Reply to: special characters db2 unload
Reply with quote

What happens when you do a hex on? What do you see in place of '.....' when hex on?
Back to top
View user's profile Send private message
Ashish.Srivastava.123

New User


Joined: 29 Jun 2014
Posts: 16
Location: India

PostPosted: Thu Jun 25, 2015 1:02 am    Post subject: Reply to: special characters db2 unload
Reply with quote

I see this.
Code:

                                                                               
------------------------------------------------------------------------------
                 ..............?    BENA2          B       ...................?
444444444444444440000000000000064444CCDCF4444444444C444444400000000000000000006
0000000000000000000000000000000F0000255120000000000200000000000000000000000000F
------------------------------------------------------------------------------
                 A             .    199805         1A      .    A             .
44444444444444444C444444444444404444FFFFFF444444444FC44444404444C44444444444440
0000000000000000010000000000000000001998050000000001100000000000100000000000000
------------------------------------------------------------------------------
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 331
Location: USA

PostPosted: Thu Jun 25, 2015 1:06 am    Post subject: Reply to: special characters db2 unload
Reply with quote

Quote:
I want to get rid of '..............? ' from DB2 unload file and replace them with blank


Ok. So, can you replace them now with blank (spaces) ?

.
Back to top
View user's profile Send private message
Ashish.Srivastava.123

New User


Joined: 29 Jun 2014
Posts: 16
Location: India

PostPosted: Thu Jun 25, 2015 1:25 am    Post subject: Reply to: special characters db2 unload
Reply with quote

How can do that with JCL , as this is supposed to be automated process . I should not be doing a manual replace.
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 331
Location: USA

PostPosted: Thu Jun 25, 2015 1:36 am    Post subject: Reply to: special characters db2 unload
Reply with quote

There are NUMEROUS examples on the internet on how to do that.

Search and Learn.

.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7236

PostPosted: Thu Jun 25, 2015 1:39 am    Post subject: Reply to: special characters db2 unload
Reply with quote

Isn't the failure of your compare warning you that something may be wrong, or telling you that something is wrong.

Just ignoring it may get you forward with something, but it shouldn't be going into a permanent process, should it?
Back to top
View user's profile Send private message
Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1219
Location: Israel

PostPosted: Thu Jun 25, 2015 12:50 pm    Post subject:
Reply with quote

Maybe DB2 returns a NULL value while ADABAS returns SPACES ??
Check the table definition.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7236

PostPosted: Thu Jun 25, 2015 1:35 pm    Post subject: Reply to: special characters db2 unload
Reply with quote

And be aware that you seem to have lots of other "dots" as well.
Back to top
View user's profile Send private message
don.leahy

Active Member


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

PostPosted: Thu Jun 25, 2015 7:42 pm    Post subject:
Reply with quote

If the DB2 unload file was created by DSNTIAUL, the "?" character to the right of the "dots" is telling you that the column is NULL.
Back to top
View user's profile Send private message
Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1219
Location: Israel

PostPosted: Sat Jun 27, 2015 9:43 am    Post subject:
Reply with quote

Quote:
DB2 Unload JCL Step

Code:
//*************************************************************
//STEP01A  EXEC DB2TEST,MOD=DSNTIAUS,     
//            SYSTEM=TEST,USERSYS=DSNX   
//SYSPUNCH DD DSN=TTTTTPR.P998.DBCNT.PUNCH,SPACE=(TRK,(1,1)), 
//            DISP=(NEW,DELETE,DELETE)                         
//SYSREC00 DD DSN=TTTTTPR.PARENT.DB2.TABLE,                   
//             DCB=(RECFM=FB,LRECL=135,BLKSIZE=0),             
//            SPACE=(CYL,(5,5),RLSE),DISP=(MOD,KEEP,KEEP)   
//SYSIN    DD *                                               
SELECT                                                     
       CHAR(CODE_NUMBER),                                 
       CHAR(CODE),                                         
       CHAR(EXPANSION),                                   
       CHAR(NAME),                                         
       CHAR(NUMBER_CODE),                                 
       CHAR(TBL_NAME),                                     
       CHAR(NUMBER_NAME)                                   
  FROM DDDDDDD.TABLE_DATA ;                               
/*


Just having a look at the SYSPUNCH file is apparently "too much information"...
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 DB2 Unload format options Susanta DB2 2 Fri Aug 12, 2016 5:42 pm
No new posts DXT unload utility - DVRE0000 mistah kurtz DB2 1 Thu Jul 07, 2016 4:56 pm
No new posts Remove Special Characters from Mainfr... Rodger Zhang All Other Mainframe Topics 6 Wed Jul 06, 2016 1:12 am
No new posts How to suppress retun code of partil ... mkk157 DB2 9 Fri May 27, 2016 6:59 am


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