Results 1 to 15 of 15
  1. #1
    Join Date
    Dec 2005
    Posts
    4

    Check table exists when creating procedure

    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

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,734
    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.
    Last edited by Marcus_A; 12-06-05 at 15:12.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,649
    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

  4. #4
    Join Date
    Dec 2005
    Posts
    4
    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!

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,649
    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

  6. #6
    Join Date
    Dec 2005
    Posts
    4
    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.

  7. #7
    Join Date
    Nov 2004
    Posts
    1,380
    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:
    Code:
    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.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  8. #8
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,649
    theramore, how do you create your procedures .. Using Development Centre ? or any other means ?

  9. #9
    Join Date
    Dec 2005
    Posts
    4
    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.

  10. #10
    Join Date
    Feb 2009
    Posts
    21
    I want an SQL script that creates a db / scheam / table after checking if that db / schema / table exists - is this possible directly inside the script?
    I understood this is possible only using a stored procedure.
    Please clarify this issue...
    I use DB2 UDB v9 under AIX/Solaris.

    I mean I want to execute inside SQL instead of:

    CREATE TABLE <table_name>(...)

    the following:

    CREATE TABLE <table_name>(...) "only if the table <table_name> does not exists..."
    I cannot use this 'isTablePresent' inside SQL right?

    Please help me to solve it,


    Thanks a lot,
    Last edited by heartwork93; 04-23-09 at 09:12.

  11. #11
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    The typical approach is to just execute the statement and ignore the error. If you really want to suppress the error, you need some sort of small application (like a Perl or PHP script) that implements the logic you're requesting.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  12. #12
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Just generate your command using SQL:
    Code:
    select varchar (
                      'db2 values current_timestamp '
                    || case to_exist_or_not_to_exist
                            when 'NOT FOUND' 
                            then space(1) 
                            else varchar (   ' && db2 drop table '
                                          || rtrim(current_schema)
                                          || '.'
                                          || rtrim(to_exist_or_not_to_exist)
                                     ,096)
                       end  
         
               ,128)
    from ( select coalesce(b.table_name,'NOT FOUND') as to_exist_or_not_to_exist
           from sysibm.sysdummy1               as a
           left outer join sysibm.sqltables    as b
                      on  current_schema                  = b.table_schem
                      and ucase('project')                = b.table_name
                      and ucase('table')                  = b.table_type 
         ) 
     ;

  13. #13
    Join Date
    Feb 2009
    Posts
    21
    I do not want to drop an existing table...
    I want to create a new table only if this table does not exist
    (and the same for a schema) - I am stil wondering if this is possible inside an SQL as below...
    ... or it is a must to use stored procedures and then call the procedure inside SQL - I don't know how to do...
    Please can you explain below SQL statemnet - why using 'sysibm.sysdummy1 as a' - it is possible to use in SQL CASE statement?



    Quote Originally Posted by dr_te_z
    Just generate your command using SQL:
    Code:
    select varchar (
                      'db2 values current_timestamp '
                    || case to_exist_or_not_to_exist
                            when 'NOT FOUND' 
                            then space(1) 
                            else varchar (   ' && db2 drop table '
                                          || rtrim(current_schema)
                                          || '.'
                                          || rtrim(to_exist_or_not_to_exist)
                                     ,096)
                       end  
         
               ,128)
    from ( select coalesce(b.table_name,'NOT FOUND') as to_exist_or_not_to_exist
           from sysibm.sysdummy1               as a
           left outer join sysibm.sqltables    as b
                      on  current_schema                  = b.table_schem
                      and ucase('project')                = b.table_name
                      and ucase('table')                  = b.table_type 
         ) 
     ;

  14. #14
    Join Date
    Feb 2009
    Posts
    21
    can someone help?

  15. #15
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    I use that construction to guarantee 1 row always as a result. The column contains the value of the table-name itself if the table exists else the text "NOT FOUND" is presented.
    So you swap actions in the case statement. When "NOT FOUND" do you thing (create the table) and leave the else empty.

Posting Permissions

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