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

special characters db2 unload


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
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
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: 446
Location: USA

PostPosted: Thu Jun 25, 2015 12:36 am
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
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: 446
Location: USA

PostPosted: Thu Jun 25, 2015 1:06 am
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
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: 446
Location: USA

PostPosted: Thu Jun 25, 2015 1:36 am
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

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Thu Jun 25, 2015 1:39 am
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: 1353
Location: Israel

PostPosted: Thu Jun 25, 2015 12:50 pm
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

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Thu Jun 25, 2015 1:35 pm
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: 765
Location: Whitby, ON, Canada

PostPosted: Thu Jun 25, 2015 7:42 pm
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: 1353
Location: Israel

PostPosted: Sat Jun 27, 2015 9:43 am
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 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 Substring number between 2 characters... DFSORT/ICETOOL 2
No new posts Remote Unload of CLOB Columns DB2 6
No new posts Multiple table unload using INZUTILB DB2 2
No new posts changing defaults in db2 admin - Unlo... DB2 0
Search our Forums:

Back to Top