Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2013
    Posts
    9

    Unanswered: Is it possible to control execution timing?

    I have 2 stored procedures that attempt to do the following:
    Procedure 1: Drops a number of columns from a table.

    Procedure 2: Recreates the same columns as different data types. This approach is taken rather than an alter table statement because we're going from a numeric to char type.

    Procedure 3: Populates the new field with data from some temporary fields in the same table that were created and populated earlier in the process flow.

    Following are snippets from the procedures.


    CREATE PROCEDURE PROD00016774_DDL2 ()
    LANGUAGE SQL
    MODIFIES SQL DATA
    BEGIN
    IF EXISTS (
    SELECT *
    FROM SYSCOLUMNS
    WHERE TABLE_NAME = 'PFLSPSVGLA'
    AND COLUMN_NAME = 'XUSR01') THEN
    ALTER TABLE PFLSPSVGLA DROP COLUMN XUSR01;
    ...
    ALTER TABLE PFLSPSVGLA DROP COLUMN XUSR40;
    END IF;
    END;
    CALL PROD00016774_DDL2;
    DROP PROCEDURE PROD00016774_DDL2;

    CREATE PROCEDURE PROD00016774_DDL3()
    LANGUAGE SQL
    BEGIN
    IF NOT EXISTS (
    SELECT *
    FROM SYSCOLUMNS
    WHERE TABLE_NAME = 'PFLSPSVGLA'
    AND COLUMN_NAME = 'XUSR01') THEN
    ALTER TABLE PFLSPSVGLA ADD XUSR01 CHAR(12);
    ...
    ALTER TABLE PFLSPSVGLA ADD XUSR40 CHAR(12);
    END IF;
    END;
    CALL PROD00016774_DDL3;
    DROP PROCEDURE PROD00016774_DDL3;

    CREATE PROCEDURE PROD00016774_DML2 ()
    LANGUAGE SQL
    MODIFIES SQL DATA
    BEGIN
    IF EXISTS (
    SELECT *
    FROM SYSCOLUMNS
    WHERE TABLE_NAME = 'PFLSPSVGLA'
    AND COLUMN_NAME IN ('XUSR01')) THEN
    UPDATE PFLSPSVGLA
    SET XUSR01 = T_XUSR01,
    ...
    XUSR40 = T_XUSR40;

    END IF;
    END;
    CALL PROD00016774_DML2;
    DROP PROCEDURE PROD00016774_DML2;


    The execution results in an error (in red below) against the final procedure because the fields it attempts to populate do not exist.


    SQL State: 42703
    Vendor Code: -205
    Message: [SQL0205] Column XUSR01 not in table PFLSPSVGLA in GI35ILC. Cause . . . . . : A column with the name XUSR01 does not exist in table or view PFLSPSVGLA in schema GI35ILC. Recovery . . . : Do one of the following and try the request again: -- Make certain that the column names, table names, and any qualifiers are specified correctly. -- If the column is not qualified, the column XUSR01 is no longer in table PFLSPSVGLA. It was originally found in table PFLSPSVGLA, but it no longer exists. If the column is now available in a different table and is referenced by this statement, a precompile may be necessary. -- If more than one table is referenced in a SQL statement, the column name should be qualified. -- If this is a CREATE TABLE statement and column XUSR01 is specified in a partitioning key or constraint for the table being created, add a column definition for column XUSR01 or remove it from the constraint or partitioning key. -- For a recursive common table expression, the SET sequence column name and the USING column name cannot be referenced in the fullselect that defines the common table expression.

    > CALL PROD00016774_DML2

    SQL State: 42704
    Vendor Code: -204
    Message: [SQL0204] PROD00016774_DML2 in GI35ILC type *N not found. Cause . . . . . : PROD00016774_DML2 in GI35ILC type *N was not found. If the member name is *ALL, the table is not partitioned. If this is an ALTER TABLE statement and the type is *N, a constraint or partition was not found. If this is not an ALTER TABLE statement and the type is *N, a function, procedure, trigger or sequence object was not found. If a function was not found, PROD00016774_DML2 is the service program that contains the function. The function will not be found unless the external name and usage name match exactly. Examine the job log for a message that gives more details on which function name is being searched for and the name that did not match. Recovery . . . : Change the name and try the request again. If the object is a node group, ensure that the DB2 Multisystem product is installed on your system and create a nodegroup with the CRTNODGRP CL command. If an external function was not found, be sure that the case of the EXTERNAL NAME on the CREATE FUNCTION statement exactly matches the case of the name exported by the service program.



    Is there a way that I can ensure that the previous procedure which creates the new fields has completed processing before allowing the final procedure from executing?

    I am using IBM System i Navigator v6r1 against
    DB2 UDB for AS/400
    07.01.0000 V7R1m0
    AS/400 Toolbox for Java JDBC Driver
    Driver Version: 8.11

    Thanks to all in advance.
    Ken

  2. #2
    Join Date
    Aug 2013
    Posts
    9

    Issue Resolved-Corrupt System Views

    After more investigation I discovered that the sys views were pointing to a different database from which this one had been copied. After rebuilding the views the process works as expected.

Posting Permissions

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