Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2011
    Posts
    3

    Question Unanswered: Finding Junk Charecters

    Hi,

    I am working for Support Project in SSIS.
    In that, One of the package is loading data from db2 to SQL server Database tables. But it is failing couple of days because of junk charecter in DB2.

    So i have created fix packsge in SSIS to redirect the rows which having junk charecters. And every time i rerunning this when the package failed because of junk charecter.

    But i dont want to waste my time to run the fix packge when it failed.

    I want to find the junk charecters in the DB2 it self,If the packge fails.

    Can any body tell me how to find the junk charecters in db2.

  2. #2
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Are you sure this is not because of a codepage mismatch?
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  3. #3
    Join Date
    Apr 2011
    Posts
    3

    Question Finding Junk Charecters

    Thanks for your reply.

    Actually i dont know about the Source. I am getting the junk charecters from Source database DB2. I want to find those junk charecters.

    So Can you Please provide me the query that

    "How to find junk charecters from DB2".

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What is the definition of junk charecters?

    I don't think " junk charecters = 'junk charecters' ".

  5. #5
    Join Date
    Apr 2011
    Posts
    3
    Junk charecters means the charecters which we cant read and we cant write mannually.

    In my database the text contains the Symbols like Square ,Epsilon..

    So I want to find all the symbols...

    How?...............

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    What does "read/write manually" mean exactly? After all, I can write an epsilon just fine on paper. With the proper character set, I can also easily set it in a text document and with a greek keyboard, you may have it on the key that usually is for the letter "E" or so.

    Basically it comes down to: how do you determine programmatically for each character whether this character is "junk" or not. Please give an algorithm for that.

    p.s: One approach could be the isprint() system function on Unix.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I agree with stolze.
    Words like "junk characters", "read/write manually" or "symbols" are not clear for all peoples, especially who are using different character set.

    If you can give a string including all junk characters, TRANSLATE function is useful to blank all junk characters or to remove all junk characters.
    For example:
    TRANSLATE(source_string , '' , junk_characters) -- blank all junk characters
    TRANSLATE(source_string , '' , junk_characters , '') -- remove all junk characters

    If you can give a string including all valid characters, you may need a little trick.

    Here is an example:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     parm(valid_cgaracters) AS (
    VALUES ' BCDFGHJKLMNPQRSTVWXYZ'
    )
    SELECT empno
         , fullname
         , TRANSLATE(  fullname
                     , ''
                     , TRANSLATE(fullname , '' , valid_cgaracters , '')
                    )
             AS junk_blanked
         , TRANSLATE(  fullname
                     , ''
                     , TRANSLATE(fullname , '' , valid_cgaracters , '')
                     , ''
                    )
             AS junk_removed_1
         , TRANSLATE(fullname , valid_cgaracters , valid_cgaracters || fullname , '')
             AS junk_removed_2
     FROM  (SELECT empno
                 , VARCHAR(firstnme || ' ' || lastname , 20) AS fullname
             FROM  employee
           ) e
         , parm
    ;
    ------------------------------------------------------------------------------
    
    EMPNO  FULLNAME             JUNK_BLANKED         JUNK_REMOVED_1       JUNK_REMOVED_2      
    ------ -------------------- -------------------- -------------------- --------------------
    000010 CHRISTINE HAAS       CHR ST N  H  S       CHRSTN HS            CHRSTN HS           
    000020 MICHAEL THOMPSON     M CH  L TH MPS N     MCHL THMPSN          MCHL THMPSN         
    000030 SALLY KWAN           S LLY KW N           SLLY KWN             SLLY KWN            
    000050 JOHN GEYER           J HN G Y R           JHN GYR              JHN GYR             
    000060 IRVING STERN          RV NG ST RN         RVNG STRN            RVNG STRN           
    000070 EVA PULASKI           V  P L SK           V PLSK               V PLSK              
    000090 EILEEN HENDERSON       L  N H ND RS N     LN HNDRSN            LN HNDRSN           
    000100 THEODORE SPENSER     TH  D R  SP NS R     THDR SPNSR           THDR SPNSR          
    000110 VINCENZO LUCCHESSI   V NC NZ  L CCH SS    VNCNZ LCCHSS         VNCNZ LCCHSS        
    000120 SEAN O'CONNELL       S  N   C NN LL       SN CNNLL             SN CNNLL            
    000130 DELORES QUINTANA     D L R S Q  NT N      DLRS QNTN            DLRS QNTN           
    000140 HEATHER NICHOLLS     H  TH R N CH LLS     HTHR NCHLLS          HTHR NCHLLS         
    000150 BRUCE ADAMSON        BR C   D MS N        BRC DMSN             BRC DMSN            
    000160 ELIZABETH PIANKA      L Z B TH P  NK      LZBTH PNK            LZBTH PNK           
    000170 MASATOSHI YOSHIMURA  M S T SH  Y SH M R   MSTSH YSHMR          MSTSH YSHMR         
    000180 MARILYN SCOUTTEN     M R LYN SC  TT N     MRLYN SCTTN          MRLYN SCTTN         
    000190 JAMES WALKER         J M S W LK R         JMS WLKR             JMS WLKR            
    000200 DAVID BROWN          D V D BR WN          DVD BRWN             DVD BRWN            
    000210 WILLIAM JONES        W LL  M J N S        WLLM JNS             WLLM JNS            
    000220 JENNIFER LUTZ        J NN F R L TZ        JNNFR LTZ            JNNFR LTZ           
    000230 JAMES JEFFERSON      J M S J FF RS N      JMS JFFRSN           JMS JFFRSN          
    000240 SALVATORE MARINO     S LV T R  M R N      SLVTR MRN            SLVTR MRN           
    000250 DANIEL SMITH         D N  L SM TH         DNL SMTH             DNL SMTH            
    000260 SYBIL JOHNSON        SYB L J HNS N        SYBL JHNSN           SYBL JHNSN          
    000270 MARIA PEREZ          M R   P R Z          MR PRZ               MR PRZ              
    000280 ETHEL SCHNEIDER       TH L SCHN  D R      THL SCHNDR           THL SCHNDR          
    000290 JOHN PARKER          J HN P RK R          JHN PRKR             JHN PRKR            
    000300 PHILIP SMITH         PH L P SM TH         PHLP SMTH            PHLP SMTH           
    000310 MAUDE SETRIGHT       M  D  S TR GHT       MD STRGHT            MD STRGHT           
    000320 RAMLAL MEHTA         R ML L M HT          RMLL MHT             RMLL MHT            
    000330 WING LEE             W NG L               WNG L                WNG L               
    000340 JASON GOUNOT         J S N G  N T         JSN GNT              JSN GNT             
    
      32 record(s) selected.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •