Tags: create, creating, database, db2, exists, iserie, mysql, oracle, procedure, select, sql, table, v5r3

Check table exists when creating procedure

On Database » DB2

4,145 words with 8 Comments; publish: Fri, 07 Dec 2007 14:56:00 GMT; (250125.00, « »)

I am new to DB2. I am using iSerie v5r3.

My question is simple. Say, I create a procedure to select data from a table. I find out that when I execute the create procedure statement, db2 does not exist if the table (I select from) is exists or not. It does not return any error until I try to 'call' the procedure and accesss the statement in question. It will waste a lot of time to check each table name by 'eyeball'.

Is there anyone know if there is any way to force DB2 to check the table is exists or not when I execute the create procedure statement?

Thanks a lot

All Comments

Leave a comment...

  • 8 Comments
    • DB2 for iSeries is quite a bit different from the other DB2 implementations. In other versions of DB2 (LUW and z/OS), the tables and columns referenced must exist when you create the SP in order for the SP to create successfully.

      If a table, column, index, etc, used in a SP is dropped after the SP is created, then the package (the SQL statements that have been bound for the SP) is marked as invalid. The first time someone tries to execute a SP with an invalid package, DB2 will execute a dynamic rebind of the package and check the SQL to make sure all referenced objects exist, and if the objects now exist the package is marked as valid and executes as normal. If the only missing object that the package was previously using is an index, then it will choose a different access path and always rebind successfully since DB2 does not need an index to execute an SQL statement (unless the SQL statement is to drop a specific index).

      If a SP is still missing any needed objects at the first rebind, then the execution of the SP will fail and the package is marked as inoperative and an explicit rebind must be performed after the needed objects are recreated.

      But as I said, I don't know how DB2 for iSeries handles this.

      #1; Tue, 11 Dec 2007 18:13:00 GMT
    • All of Marcus comments relate to static SQL Statements .. Remember, if you are using dynamic SQL within your procedure, then all validations are done at runtime and not bind time...

      Cheers

      Sathyaram

      #2; Tue, 11 Dec 2007 18:14:00 GMT
    • Thanks.

      For Oracle and Sqlserver and DB2 (from the information provided by Marcus), all of them will check the table referenced (or any object referenced) exist before a procedure can be successfully compiled. However, it seems to me that it is not the case for iSeries OR at least not the default way that iSeries to handle 'create procedure'.

      May I ask if there is any way to enable the checking...?

      Thanks a lot!

      #3; Tue, 11 Dec 2007 18:15:00 GMT
    • There's a bind option VALIDATE which can be either BIND or RUN ... If you specify run for the option, then object checking will happen at runtime ... Could be that ..

      I don't remember to have tried VALIDATE RUN , so am unable to confirm this could be the case ...

      For your case, you need to have VALIDATE BIND

      Please feedback the forum on your findings ..

      Cheers

      Sathyaram

      #4; Tue, 11 Dec 2007 18:16:00 GMT
    • Thanks... As I am very new to DB2 and iSeries, I don't even know how/where to change the bind option... I checked some reference manual already but did not get any idea. Any more hint? Thanks a lot.
      #5; Tue, 11 Dec 2007 18:17:00 GMT
    • You can also consult the DB2 system tables. They contain information about all the objects in the database, like tables, indexes, columns, FK's, ...

      To check if a table MYSCHEMANAME.MYTABLENAME exists, use:

      SELECT COUNT(*)

      INTO :isTablePresent

      FROM FROM SYSCAT.TABLES

      WHERE SYSCAT.TABLES.TABSCHEMA = 'MYSCHEMANAME' AND

      SYSCAT.TABLES.TABNAME = 'MYTABLENAME'

      ; If isTablePresent == 0 then the table does not exist, if isTablePresent == 1 the table exists, other values are impossible.

      #6; Tue, 11 Dec 2007 18:18:00 GMT
    • theramore, how do you create your procedures .. Using Development Centre ? or any other means ?
      #7; Tue, 11 Dec 2007 18:19:00 GMT
    • sathyaram,

      Thanks. the db is in a remote site, I am using iSeries Access for window to access the database (by iSeries navigator). In the navigator, there is a tools to run a SQL scripts. I am using that to create procedures.

      wim,

      thanks. my question is a bit different from what you answered but anyway thanks a lot.

      #8; Tue, 11 Dec 2007 18:20:00 GMT