Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2007
    Posts
    6

    Unhappy Unanswered: DB2 TIMESTAMP to STRING

    HI,

    I migrate from v7 to v9 and I have a problem when my application wants to display a db2 timestamp data in a string field.

    In v7, we used the following lines in the db2cli.ini:

    PATCH1=131072
    LOBMAXCOLUMNSIZE=1048575
    LONGDATACOMPAT=1

    But PATCH1=131072 is now depreciated.

    So I tried to add:

    DateTimeStringFormat=ISO
    MapTimestampDescribe = 1
    MapTimestampCDefault = 1
    PATCH2=33

    and now I have the CLI message "CLI0111E Numeric value out of range. SQLSTATE=22033" !

    How could I have the same behaviour that with the old configuration
    "PATCH1=131072" ?

    Could you help me please? It's very important.

    Thanks,
    Lad.

    PS: Excuse me for my bad english.

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    what was the patch for and which date format do you need ??
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Mar 2007
    Posts
    6
    I want these formats:
    for timestamp data : 'YYYY-MM-DD-00.00.00.000000'
    for date data: 'YYYY-MM-DD'

    We had in the v7 db2cli.ini :

    [MyDBAlias]
    PATCH1=131072
    LOBMAXCOLUMNSIZE=1048575
    LONGDATACOMPAT=1
    DESCRIPTION=
    DBALIAS=MYDBALIAS

    We have in the v9 db2cli.ini

    [MyDBAlias]
    DateTimeStringFormat=ISO
    MapTimestampDescribe = 1
    MapTimestampCDefault = 1
    PATCH2="24,33"
    DBALIAS=MYDBALIAS

    In the command center it seems OK now (with the new patch1 and patch2 values) but from my application I have the CLI0111E error.

    An idea?

  4. #4
    Join Date
    Mar 2007
    Posts
    6
    Hi,

    I put the trace

    -With the DB2 7.2 client :

    SQLDescribeCol( hStmt=1:3, iCol=3, pszColName=&00215978, cbColNameMax=32, pcbColName=&002159a4, pfSQLType=&002159b4, pcbColDef=&002159c4, pibScale=&002159d8, pfNullable=&002159e8 )
    ---> Time elapsed - +3,270000E-003 seconds

    SQLDescribeCol( pszColName="DATE_TRAIT_FICHIER" - X"444154455F54524149545F46494348494552", pcbColName=18, pfSQLType=SQL_CHAR, pcbColDef=26, pibScale=0, pfNullable=SQL_NO_NULLS )
    <--- SQL_SUCCESS Time elapsed - +7,004200E-002 seconds

    SQLColAttributes( hStmt=1:3, iCol=3, fDescType=SQL_COLUMN_LENGTH, rgbDesc=NULL, cbDescMax=0, pcbDesc=NULL, pfDesc=&00215b68 )
    ---> Time elapsed - +3,454000E-003 seconds

    SQLColAttributes( pfDesc=26 )
    <--- SQL_SUCCESS Time elapsed - +3,463800E-002 seconds

    SQLColAttributes( hStmt=1:3, iCol=3, fDescType=SQL_DESC_DISTINCT_TYPE, rgbDesc=&00215c20, cbDescMax=28, pcbDesc=&00215c48, pfDesc=NULL )
    ---> Time elapsed - +3,369000E-003 seconds

    SQLColAttributes( rgbDesc="" - X"", pcbDesc=0 )
    <--- SQL_SUCCESS Time elapsed - +3,856000E-002 seconds



    SQLBindCol( hStmt=1:3, iCol=3, fCType=SQL_C_CHAR, rgbValue=&00234b4a, cbValueMax=27, pcbValue=&00234bf0 )
    ---> Time elapsed - +3,048000E-003 seconds
    SQLBindCol( )
    <--- SQL_SUCCESS Time elapsed - +2,933600E-002 seconds

    SQLExecute( hStmt=1:3 )
    ---> Time elapsed - +3,312000E-003 seconds
    sqlccsend( ulBytes - 232 )
    sqlccsend( Handle - 40082160 )
    sqlccsend( ) - rc - 0, time elapsed - +6,513000E-003
    sqlccrecv( )
    sqlccrecv( ulBytes - 32646 ) - rc - 0, time elapsed - +6,424400E-002

    SQLExecute( )
    <--- SQL_SUCCESS Time elapsed - +9,049500E-002 seconds

    SQLFetch( hStmt=1:3 )
    ---> Time elapsed - +3,573000E-003 seconds

    SQLFetch( )
    <--- SQL_SUCCESS Time elapsed - +1,260600E-002 seconds
    ( iCol=1, fCType=SQL_C_LONG, rgbValue=12, pcbValue=4 )

    ( iCol=2, fCType=SQL_C_TYPE_DATE, rgbValue=2004-12-01, pcbValue=6 )

    ( iCol=3, fCType=SQL_C_CHAR, rgbValue="2004-12-01-09.38.05.000000" - X"323030342D31322D30312D30392E33382E30352E30303030 3030", pcbValue=26 )
    - With the DB2 9.7 client:
    SQLDescribeCol( hStmt=1:3, sCol=3, pszColName=&00200be4, sColNameMaxByteLen=32, psColNameCharLen=&00200c10, psSQLType=&00200c20, puiColDef=&00200c30, psScale=&00200c44, psNullable=&00200c54 )
    ---> Time elapsed - +3,093000E-003 seconds

    SQLDescribeCol( pszColName="DATE_TRAIT_FICHIER", psColNameCharLen=18, psSQLType=SQL_CHAR, pcbColDef=26, psScale=6, psNullable=SQL_NO_NULLS )
    <--- SQL_SUCCESS Time elapsed - +5,790800E-002 seconds

    SQLColAttributes( hStmt=1:3, iCol=3, sDescType=SQL_COLUMN_LENGTH, rgbDesc=<NULL pointer>, cbDescMax=0, pcbDesc=<NULL pointer>, pfDesc=&00200dd4 )
    ---> Time elapsed - +3,080000E-003 seconds

    SQLColAttributes( piNumericAttribute=16 )
    <--- SQL_SUCCESS Time elapsed - +3,634100E-002 seconds

    SQLColAttributes( hStmt=1:3, iCol=3, sDescType=SQL_DESC_DISTINCT_TYPE, rgbDesc=&00200e8c, cbDescMax=28, pcbDesc=&00200eb4, pfDesc=<NULL pointer> )
    ---> Time elapsed - +3,034000E-003 seconds

    SQLColAttributes( pCharAttribute="", psCharAttributeByteLen=0 )
    <--- SQL_SUCCESS Time elapsed - +4,394000E-002 seconds


    SQLBindCol( hStmt=1:3, iCol=3, fCType=SQL_C_CHAR, rgbValue=&0024ad2a, cbValueMax=17, pcbValue=&0024adc8 )
    ---> Time elapsed - +3,526000E-003 seconds

    SQLBindCol( )
    <--- SQL_SUCCESS Time elapsed - +2,932100E-002 seconds

    SQLExecute( hStmt=1:3 )
    ---> Time elapsed - +4,200000E-003 seconds
    ( Requested Cursor Attributes=x'03000008' )
    ( Reply Cursor Attributes=x'0A000800' )
    ( Actual Cursor Attributes=x'0B000800' )

    SQLExecute( )
    <--- SQL_SUCCESS Time elapsed - +9,772500E-002 seconds

    SQLFetch( hStmt=1:3 )
    ---> Time elapsed - +3,104000E-003 seconds


    ( iConvFunction=363, iConv=363, iCol=3, iRow=1,
    pSourceBuff=&0334008a, pTargetBuff=&0024ad2a,
    iSourceBuffSize=32757, iSourceBytesLeft=32715, iTargetBuffSize=17, iTargetBytesWritten=4,
    iTracePoint=907, iTraceFunction=424673658,
    Current Source Buffer Address=&03340060,
    Current Source Buffer Contents (Data)=x'FF009F050000323031302D30362D3138323031302 D30362D31382D32302E35...- A very long list of characters - 02020202020' )
    ( pBoParms->pSqldd=" SQLDD Information
    ~~~~~~~~~~~~~~~~~
    numAllocVals = 19
    numVals = 14
    numExtDtaVals = 0
    numNonNullExtDtaVals = 0
    numSqlArrayVals = 0
    numNonDefScaleTS = 0
    SQLAM level received in reply = 8
    Default Data Type Definition = 2
    Max Row Size = 162
    flags = 2
    codepage = 1252
    singleByteCP = 1252
    doubleByteCP = 0
    xmlCP = 1208
    DDvalue[0] DDtype = 4, DDlen = 4, DDExtent = 0, DDcodepage = 0, DDExtDtaLenSize = 0
    DDvalue[1] DDtype = 56, DDlen = 10, DDExtent = 0, DDcodepage = 1252, DDExtDtaLenSize = 0
    DDvalue[2] DDtype = 60, DDlen = 26, DDExtent = 0, DDcodepage = 1252, DDExtDtaLenSize = 0
    DDvalue[3] DDtype = 72, DDlen = 15, DDExtent = 0, DDcodepage = 1252, DDExtDtaLenSize = 0
    DDvalue[4] DDtype = 56, DDlen = 10, DDExtent = 0, DDcodepage = 1252, DDExtDtaLenSize = 0
    DDvalue[5] DDtype = 73, DDlen = 12, DDExtent = 0, DDcodepage = 1252, DDExtDtaLenSize = 0
    DDvalue[6] DDtype = 73, DDlen = 12, DDExtent = 0, DDcodepage = 1252, DDExtDtaLenSize = 0
    DDvalue[7] DDtype = 72, DDlen = 4, DDExtent = 0, DDcodepage = 1252, DDExtDtaLenSize = 0
    DDvalue[8] DDtype = 72, DDlen = 4, DDExtent = 0, DDcodepage = 1252, DDExtDtaLenSize = 0
    DDvalue[9] DDtype = 56, DDlen = 10, DDExtent = 0, DDcodepage = 1252, DDExtDtaLenSize = 0
    DDvalue[10] DDtype = 56, DDlen = 10, DDExtent = 0, DDcodepage = 1252, DDExtDtaLenSize = 0
    DDvalue[11] DDtype = 74, DDlen = 3, DDExtent = 0, DDcodepage = 1252, DDExtDtaLenSize = 0
    DDvalue[12] DDtype = 74, DDlen = 3, DDExtent = 0, DDcodepage = 1252, DDExtDtaLenSize = 0
    DDvalue[13] DDtype = 73, DDlen = 32, DDExtent = 0, DDcodepage = 1252, DDExtDtaLenSize = 0
    There is no extension for this dataDescriptor
    " )

    SQLFetch( )
    <--- SQL_ERROR Time elapsed - +9,758200E-002 seconds

    SQLError( hEnv=0:1, hDbc=0:0, hStmt=1:3, pszSqlState=&002086a8, pfNativeError=&002086bc, pszErrorMsg=&002086cc, cbErrorMsgMax=511, pcbErrorMsg=&002088d8 )
    ---> Time elapsed - +3,062000E-003 seconds
    ( iRowNumber=1, iColumnNumber=3 )

    SQLError( pszSqlState="22003", pfNativeError=-99999, pszErrorMsg="[IBM][CLI Driver] CLI0111E Valeur numérique hors plage. SQLSTATE=22003", pcbErrorMsg=71 )
    <--- SQL_SUCCESS Time elapsed - +6,111600E-002 seconds

    SQLGetSqlca( hEnv=0:1, hDbc=0:0, hStmt=1:3, pSqlcaOut=&00209398 )
    ---> Time elapsed - +3,802000E-003 seconds

    SQLGetSqlca( )
    <--- SQL_SUCCESS Time elapsed - +2,184600E-002 seconds

    SQLError( hEnv=0:1, hDbc=0:0, hStmt=1:3, pszSqlState=&002097e0, pfNativeError=&002097f4, pszErrorMsg=&00209804, cbErrorMsgMax=511, pcbErrorMsg=&00209a10 )
    ---> Time elapsed - +3,083000E-003 seconds

    SQLError( )
    <--- SQL_NO_DATA_FOUND Time elapsed - +3,976400E-002 seconds

    Maybe it could help you to have an idea!

Posting Permissions

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