Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2007
    Posts
    36

    Unanswered: Query results are not showing correct.

    Hi,
    I've an existing table which has acct id as char(10).
    When this field is being searched in quotes there are no records showing even the data exists.
    But its taking the field as integer (with out quotes) and fetching correct results.
    This is not correct. And also it's not showing result if I search with other column for same record. I'm using Db2 V10.1 in Z/OS.
    This is happening in test database only not in production.
    I applied Runstats and reorg but no use. I'll try to load the data again and will check.
    Can anyone help me how to correct this issue? and please explain why this happens?
    Thanks,
    Anil

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please publish ...
    (1) CREATE TABLE statement.
    (2) The way to populate the table.
    (2-1) INSERT INTO ... statement.
    or
    (2-2) LOAD command with source data file.
    (3) The results you expected from the data provided by (2).
    (4) The query(SELECT statement) you issued.
    (5) The results you got by the query(4) issued on the data(2).

  3. #3
    Join Date
    Nov 2007
    Posts
    36
    1. cREATE TABLE STATEMENT.


    CREATE TABLE TEST001.ACCT_TBL_REF
    (REF_ID BIGINT
    NOT NULL
    ,INS_ID CHARACTER(10) FOR SBCS DATA
    NOT NULL
    WITH DEFAULT
    ,PRD_ID CHARACTER(3) FOR SBCS DATA
    NOT NULL
    WITH DEFAULT
    ,ACT_ID CHARACTER(10) FOR SBCS DATA
    NOT NULL
    WITH DEFAULT
    ,STRT_DT DATE
    NOT NULL


    ..... AND OTHER COLUMNS.....


    2. The table is recently loaded from a file taken using unload from production.



    Unload:
    UNLOAD TABLESPACE
    testDB001.tsTS001
    DB2 NO QUIESCE NO QUIESCECAT NO
    LOCK NO
    SELECT * FROM
    testDB001.ACCT_TBL_REF
    OUTDDN (HPULD)
    FORMAT DSNTIAUL
    LOADDDN HPUPCH00 LOADOPT
    (LOG NO REPLACE SORTKEYS &SORTKEYS
    INDDN SYSREC00)



    Load Card:

    LOAD DATA
    LOG NO REPLACE SORTKEYS 139067490 INDDN SYSREC00
    EBCDIC CCSID(0037)

    INTO TABLE testDB001.ACCT_TBL_REF
    (
    REF_ID
    POSITION ( 1 ) BIGINT
    ,
    INS_ID
    POSITION ( 9 ) CHAR ( 10 )
    ,
    PRD_ID
    POSITION ( 19 ) CHAR ( 3 )
    ,
    ACT_ID
    POSITION ( 22 ) CHAR ( 23 )
    ,
    STRT_DT
    POSITION ( 45 ) DATE EXTERNAL ( 10 )



    3. Query that I'm looking for rows:




    SELECT * FROM testDB001.ACCT_TBL_REF fetch first rows only with ur;
    result:


    --+---------+---------+---------+---------+---------+---------+
    REF_ID INS_ID PRD_ID ACT_ID STRT_DT
    --+---------+---------+---------+---------+---------+---------+
    11111 1223344541 819 1245767898900990000001 09/21/2010


    SELECT * FROM testDB001.ACCT_TBL_REF where ACT_ID ='1245767898900990000001' with ur;
    Result: No records

    SELECT * FROM testDB001.ACCT_TBL_REF where ACT_ID =1245767898900990000001 with ur;

    result:
    --+---------+---------+---------+---------+---------+---------+
    REF_ID INS_ID PRD_ID ACT_ID STRT_DT
    --+---------+---------+---------+---------+---------+---------+
    11111 1223344541 819 1245767898900990000001 09/21/2010


    SELECT * FROM testDB001.ACCT_TBL_REF where REF_ID=11111
    No result



    Note: I've changed the db name, tbl name, column names etc.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Something strange was

    CREATE TABLE TEST001.ACCT_TBL_REF
    (REF_ID BIGINT
    ...
    ,ACT_ID CHARACTER(10) FOR SBCS DATA
    NOT NULL
    WITH DEFAULT
    and
    SELECT * FROM testDB001.ACCT_TBL_REF fetch first rows only with ur;
    result:


    --+---------+---------+---------+---------+---------+---------+
    REF_ID INS_ID PRD_ID ACT_ID STRT_DT
    --+---------+---------+---------+---------+---------+---------+
    11111 1223344541 819 1245767898900990000001 09/21/2010
    The value of ACT_ID showed
    1245767898900990000001
    (22 char!)
    How to store 22 char data into a column of CHARACTER(10)?

    I think this is contradiction.


    Another possibility I thought was that the column attribute in a table testDB001.ACCT_TBL_REF(target of load) might be different from a table TEST001.ACCT_TBL_REF(source of load).
    If so,
    please publish DDL of a table testDB001.ACCT_TBL_REF(target of load), additional to a source table(TEST001.ACCT_TBL_REF).



    Even if so more my question ...

    You unloaded like...
    Unload:
    UNLOAD TABLESPACE
    testDB001.tsTS001
    DB2 NO QUIESCE NO QUIESCECAT NO
    LOCK NO
    SELECT * FROM
    testDB001.ACCT_TBL_REF
    OUTDDN (HPULD)
    FORMAT DSNTIAUL
    LOADDDN HPUPCH00 LOADOPT
    (LOG NO REPLACE SORTKEYS &SORTKEYS
    INDDN SYSREC00)
    How did you loaded the data like '1245767898900990000001' from the data which was unloaded from a column of a table having ACT_ID CHARACTER(10)?

  5. #5
    Join Date
    Nov 2007
    Posts
    36
    Thanks for that.
    ya its my bad, it was 23 char field.
    We loaded the table again with same control cards.
    now its working fine.

Posting Permissions

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