If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > Check table exists when creating procedure

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: May 2003
Location: USA
Posts: 5,719
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.
__________________
M. A. Feldman
IBM Certified DBA on DB2 for Linux, UNIX, and Windows
IBM Certified DBA on DB2 for z/OS and OS/390

Last edited by Marcus_A; 12-06-05 at 15:12.
Reply With Quote
  #3 (permalink)  
Old
Super Moderator
 
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
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
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!
Reply With Quote
  #5 (permalink)  
Old
Super Moderator
 
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
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Nov 2004
Posts: 1,364
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 Earned beers: 16
Wim
Beware of bugs in the above code; I have only proved it correct, not tried it. -- Donald Knuth
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
Reply With Quote
  #8 (permalink)  
Old
Super Moderator
 
Join Date: Aug 2001
Location: UK
Posts: 4,649
theramore, how do you create your procedures .. Using Development Centre ? or any other means ?
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #11 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #12 (permalink)  
Old
Registered User
 
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 
     ) 
 ;
Reply With Quote
  #13 (permalink)  
Old
Registered User
 
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 
     ) 
 ;
Reply With Quote
  #14 (permalink)  
Old
Registered User
 
Join Date: Feb 2009
Posts: 21
can someone help?
Reply With Quote
  #15 (permalink)  
Old
Registered User
 
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.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On