Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2011
    Posts
    20

    Unanswered: DB2 ALTOBJ SQL0444N Error

    Hi

    Can anyone please let me know why the below error is encountered for ALTOBJ invocation to rename a column name. What is the probable solution ?

    db9sr001 is the instance

    create table TEST_ALT ( c1 char(10), c2 integer)

    create view V_TEST_ALT as select * from TEST_ALT;



    db9sr001@c01z0090:/db2/db9sr001> db2 "CALL SYSPROC.ALTOBJ( 'APPLY_STOP_ON_ERROR','create table TEST_ALT (c3 char(10), c2 integer) ', -1, ?) "
    SQL0443N Routine "ALTOBJ" (specific name "") has returned an error SQLSTATE
    with diagnostic text "SQL0444 Token:
    SYSPROC.DB2LOAD|DB2LOAD|.../sqllib/bin/routine/". SQLSTATE=38553



    2012-06-18-10.44.53.694379+000 E200172995A549 LEVEL: Warning
    PID : 24220 TID : 2199060645792PROC : db2fmp (
    INSTANCE: db9sr001 NODE : 000
    FUNCTION: DB2 UDB, routine_infrastructure, sqlerFencedTruncateTokensUserSqlState, probe:100
    DATA #1 : signed integer, 4 bytes
    -443
    DATA #2 : String with size, 5 bytes
    38553
    DATA #3 : String with size, 14 bytes
    SYSPROC.ALTOBJ
    DATA #4 : String with size, 6 bytes
    ALTOBJ
    DATA #5 : String with size, 69 bytes
    SQL0444 Token: SYSPROC.DB2LOAD|DB2LOAD|.../sqllib/bin/routine/db2load



    sqlerFencedTruncateTokensUserSqlState

  2. #2
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    What is your DB2-Version and Fixpack, and Operating-system name+version ?

  3. #3
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    it worked for me :
    (0)[db2inst2@dlx00031 work]$ db2 "create table TEST_ALT ( c1 char(10), c2 integer)"
    DB20000I The SQL command completed successfully.
    (0)[db2inst2@dlx00031 work]$ db2 "create view V_TEST_ALT as select * from TEST_ALT"
    DB20000I The SQL command completed successfully.
    (0)[db2inst2@dlx00031 work]$ db2 "CALL SYSPROC.ALTOBJ( 'APPLY_STOP_ON_ERROR','create table TEST_ALT (c3 char(10), c2 integer) ', -1, ?) "

    Value of output parameters
    --------------------------
    Parameter Name : ALTER_ID
    Parameter Value : 1

    Parameter Name : MSG
    Parameter Value : SELECT OBJ_TYPE, OBJ_SCHEMA, OBJ_NAME, SQL_OPERATION, SQL_STMT, EXEC_MODE, EXEC_SEQ FROM SYSTOOLS.ALTOBJ_INFO_V WHERE ALTER_ID=1 AND EXEC_MODE LIKE '_1______' ORDER BY EXEC_SEQ

    Return Status = 0
    (0)[db2inst2@dlx00031 work]$ db2 describe table TEST_ALT

    Data type Column
    Column name schema Data type name Length Scale Nulls
    ------------------------------- --------- ------------------- ---------- ----- ------
    C3 SYSIBM CHARACTER 10 0 Yes
    C2 SYSIBM INTEGER 4 0 Yes

    2 record(s) selected.
    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
    Mar 2011
    Posts
    20
    Hi

    Thank you for the replies.

    Apologize for missing the information

    Below is the information requested for.

    Could the issue be cause of permissions or any path configuration ?

    db2inst1@c01z0090:~> uname -a
    Linux c01z0090 2.6.16.60-0.77.1-default #1 SMP Mon Mar 14 16:54:35 UTC 2011 s390 x s390x s390x GNU/Linux
    db2inst1@c01z0090:~> db2level
    DB21085I Instance "db9sr001" uses "64" bits and DB2 code release "SQL09056"
    with level identifier "06070107".
    Informational tokens are "DB2 v9.5.0.6", "s100918", "IP23211", and Fix Pack
    "6a".
    Product is installed at "/opt/IBM/db2/V9.5".

  5. #5
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Is that Linux on Z/OS?
    Possibly a fixpack issue - fixpack 9 is more recent , if you have a test environment why not compare...

  6. #6
    Join Date
    Mar 2011
    Posts
    20
    Hi

    It is zlinux .

    I am not sure if it could be a fixpack issue as it worked for others.

    thank you

  7. #7
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    "worked for others" other tables? other instances? other environments? other types of alterations on tables?

    Your sample works fine for me (but I have no access to zLinux) - so you have something on your environment that only you can discover...

    If you find a case/environment where it works, maybe you can compare the working and failing environments?

    Or do some more problem determination: increase diaglevel and check if there's any clues in db2diag.log, check system message log, check altobj by varying the task slightly (example: does it make any difference if the table contains a row of data before being altered?).

    Your alteration seems to drop column c3, add column c1 with the same datatype+length (rename column).
    What do you expect the db2load step to do in this case, for this alteration, if the table had been populated before the alteration?

  8. #8
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    I got the same error with v9.5 FP8 on z/Linux. db2diag.log contains the following message with diaglevel 4:

    FUNCTION: DB2 UDB, oper system services, sqlofica, probe:10
    DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
    sqlcaid : SQLCA sqlcabc: 136 sqlcode: -444 sqlerrml: 64
    sqlerrmc: SYSPROC.DB2LOAD DB2LOAD .../sqllib/bin/routine/db2load db2load 4
    sqlerrp : SQLERLIB
    sqlerrd : (1) 0x00000000 (2) 0x00000000 (3) 0x00000000
    (4) 0x00000000 (5) 0x00000000 (6) 0x00000000
    sqlwarn : (1) (2) (3) (4) (5) (6)
    (7) (8) (9) (10) (11)
    sqlstate: 42724



    SYSPROC.DB2LOAD is failing with sql0444n RC=4


    RC 4 is:

    4

    The file in "<library-or-path>" could not be found, because of
    one of the following reasons:
    ....


    I checked $HOME/sqllib/bin/routine and db2load is not there (the installation dir doesn't have it as well). I also checked another z/Linux server with v9.7 FP4 - db2load is not found, but I do see it on other platforms.


    I did a test on AIX - renamed db2load and rerun SYSPROC.ALTOBJ - it failed with the same error. So, it looks like the reason for failure is a missing db2load routine. Not sure why it's not shipped on z/Linux. You can open a pmr to find out.

  9. #9
    Join Date
    Mar 2012
    Location
    Canberra, Australia
    Posts
    38
    yep that's why they have the SQLCODE in the doco with the RC - you read it and it tells you something useful, well most of the time anyway

  10. #10
    Join Date
    Mar 2011
    Posts
    20
    So, do we have to update DB2 for that ? or can we copy that module from a different system . Why could that be missed in the first place. How could that be possible ?

    Thank you

  11. #11
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Open a PMR with IBM

  12. #12
    Join Date
    Mar 2011
    Posts
    20
    DB2load routine is found to be absent in zlinux machines for db2 v9.5 ( and higher probably) . Does anyone know if we can import it from different machine? If it is removed ( mentioned in one of the forums that is not supported from v9.5 due to errors in v9.1 partitioning) what could be the possible alternative to ensure that the ALTOBJ runs ( a substitute ?)

Posting Permissions

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