Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2007
    Posts
    47

    Unanswered: alter table on clob

    Using v8 FP 12.

    trying to change a column to use logging on clob, but (I can see 10485760 in original DDL):


    CALL SYSPROC.ALTOBJ ( 'APPLY_CONTINUE_ON_ERROR', 'CREATE TABLE GURU.TKT00150 ( ID INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1, CACHE 20) , BRANCH VARCHAR (10) NOT NULL , CUSTOMER_COMMENTS CLOB (10485760 ) LOGGED NOT COMPACT , ) IN USERSPACE1 ', -1, ? )

    Error is:

    ------------------------------ Commands Entered ------------------------------
    CALL SYSPROC.ALTOBJ ( 'APPLY_CONTINUE_ON_ERROR', 'CREATE TABLE KTGURU.TKT015 ( ID INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1, CACHE 20) , BRANCH VARCHAR (10) NOT NULL , CUSTOMER_COMMENTS CLOB (10485760 ) LOGGED NOT COMPACT , ) IN USERSPACE1 ', -1, ? );
    ------------------------------------------------------------------------------

    SQL0443N Routine "ALTOBJ" (specific name "") has returned an error SQLSTATE
    with diagnostic text "SQL0204 Reason code or token: KTGURU.OE_ISSUE
    ". SQLSTATE=38553

    SQL0443N Routine "ALTOBJ" (specific name "") has returned an error SQLSTATE with diagnostic text "SQL0204 Reason code or token: GURU.ISSUE ".

    Explanation:

    An SQLSTATE was returned to DB2 by routine "<routine-name>"
    (specific name "<specific-name>"), along with message text
    "<text>". The routine could be a user-defined function or a
    user-defined method.

    User Response:

    The user will need to understand the meaning of the error. See
    your Database Administrator, or the author of the routine.

    Errors that are detected by the IBM supplied functions in the
    SYSFUN schema all return the SQLSTATE 38552. The message text
    portion of the message is of the form:


    SYSFUN:nn


    where nn is a reason code meaning:


    01 Numeric value out of range

    02 Division by zero

    03 Arithmetic overflow or underflow

    04 Invalid date format

    05 Invalid time format

    06 Invalid timestamp format

    07 Invalid character representation of a timestamp duration

    08 Invalid interval type (must be one of 1, 2, 4, 8, 16, 32, 64,
    128, 256)

    09 String too long

    10 Length or position in string function out of range

    11 Invalid character representation of a floating point number

    12 Out of memory

    13 Unexpected error

    Errors that are detected by the IBM supplied routines in the
    SYSIBM or SYSPROC schemas and IBM supplied procedures in the
    SYSFUN schema all return the SQLSTATE 38553. The message text
    portion of the message contains a message number that may be an
    SQLCODE, (for example, SQL0572N), a DBA error message (for
    example, DBA4747), or some other indication from the routine as
    to what error was encountered. Note that if the message for the
    message number would normally contain tokens, these token values
    are only available in the db2diag.log file.

    sqlcode : -443

    sqlstate : (the SQLSTATE returned by the routine).


    ==========

    I also tried using the control center gui, when trying alter, it forces a new value for the clob, only allowing bytes, kbytes, meg and gig. When I do generate DDL, I get the 10485760.
    Last edited by bic; 02-26-09 at 14:42.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    You are looking at the wrong thing. This is your problem:

    SQL0443N Routine "ALTOBJ" (specific name "") has returned an error SQLSTATE
    with diagnostic text "SQL0204 Reason code or token: KTGURU.OE_ISSUE
    ". SQLSTATE=38553
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    is this correct ?
    NOT COMPACT , the last komma
    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

  4. #4
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    is this correct ?
    NOT COMPACT , the last comma
    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

  5. #5
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    there seems to be a problem and keeps adding the message
    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

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    This comma is wrong.

    And the problem is this message as n_i said:
    SQL0443N Routine "ALTOBJ" (specific name "") has returned an error SQLSTATE
    with diagnostic text "SQL0204 Reason code or token: KTGURU.OE_ISSUE
    ". SQLSTATE=38553
    The SQL0204 error tells you that an object named KTGURU.OE_ISSUE does not exist. Whatever that is, you have to figure out in your system. Maybe the statement you posted here doesn't match with the error message, i.e. you tried different statements and grabbed the wrong message?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Feb 2007
    Posts
    47
    Quote Originally Posted by stolze
    This comma is wrong.

    And the problem is this message as n_i said:


    The SQL0204 error tells you that an object named KTGURU.OE_ISSUE does not exist. Whatever that is, you have to figure out in your system. Maybe the statement you posted here doesn't match with the error message, i.e. you tried different statements and grabbed the wrong message?

    yea, I was trying to remove the company specific table names in the post. That one slipped through... It seems that this test db has some stuff that is missing as compared to the prod db.

    Anyhow, was trying to change lob fields to be logged, now there's other problems (see my Control Center restore gone bad thread).

Posting Permissions

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