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

What is your DB2-Version and Fixpack, and Operating-system name+version ?

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.

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".

Is that Linux on Z/OS?
Possibly a fixpack issue - fixpack 9 is more recent , if you have a test environment why not compare...

Hi

It is zlinux .

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

thank you

"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?

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.

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

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

Open a PMR with IBM

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 ?)