Systems Engineering and RDBMS

Checking for the existence of a column in a table – DB2

Posted by decipherinfosys on September 13, 2007

In one of our previous post, we had covered how to check for column existence in Oracle and SQL Server.  In this post, we will cover this for DB2 LUW.  One way to add the column non-destructively in DB2 LUW is via stored procedure. In this blog post, we will show you how we can achieve that. Following is the stored procedure code to add column in the table only if it does not exist. If it exists in the column then we will not perform any action in the procedure code. We have used ‘@’ as statement terminator. Let us first create the table and then stored procedure. You can save following code into file and run it from the db2 command line or can connect to db2 command center and run it from there.

CREATE TABLE TEST
(
TEST_ID INT,
TEST_NAME VARCHAR(50)
)@

CREATE PROCEDURE alter_table
( IN tab_name VARCHAR(30), IN col_Name VARCHAR(30),
IN Data_Type VARCHAR(30), IN DATA_LEN VARCHAR(20),
IN Def_Value VARCHAR(20), IN NULLABLE CHAR(1) )
LANGUAGE SQL
SPECIFIC altable
BEGIN

DECLARE V_COUNT INT DEFAULT 0;
DECLARE STATEMENT VARCHAR(1000);

IF DATA_TYPE IN (‘VARCHAR’,’CHAR’,’NUMERIC’) AND DATA_LEN IS NULL THEN
SIGNAL SQLSTATE ‘80000’
SET MESSAGE_TEXT=’Column length is required’;
END IF;

SELECT COUNT(*) INTO v_Count
FROM SYSCAT.COLUMNS
WHERE TABNAME = tab_name
AND COLNAME = col_name;

IF (v_count = 0) THEN
SET statement = ‘ALTER TABLE ‘ || tab_Name || ‘ ADD ‘ || col_name || ‘ ‘ || DATA_TYPE || ‘ ‘ ||
CASE WHEN DATA_LEN IS NOT NULL THEN ‘(‘ || DATA_LEN || ‘)’
ELSE ”
END ||
CASE WHEN DEF_VALUE IS NOT NULL THEN ‘ DEFAULT ‘ ||
CASE WHEN DATA_TYPE IN (‘CHAR’,’VARCHAR’) THEN ””
ELSE ”
END ||
DEF_VALUE ||
CASE WHEN DATA_TYPE IN (‘CHAR’,’VARCHAR’) THEN ””
ELSE ”
END
ELSE ”
END ||
CASE WHEN COALESCE(NULLABLE,’Y’) = ‘N’ THEN ‘ NOT NULL ‘
ELSE ”
END;
EXECUTE IMMEDIATE statement;
END IF;
END@

This is very simple stored procedure, which takes 6 arguments and they are pretty self explanatory. We can check whether column exists in the table or not by querying COLUMNS catalog view of SYSCAT schema. SYSCAT schema contains all the metadata information. SYSCAT.COLUMNS has one entry for each column in the database. In our procedure, if the count is 0 (column does not exist) then we go ahead and build the string based on the given input and then execute it using execute immediate statement.

Let us first check what columns are there in the table. We should see only two columns.

SELECT * FROM syscat.columns WHERE tabname = ‘TEST’@

Above query will show only two columns for TEST table. Now we will create one more column by executing stored procedure.

CALL alter_table (‘TEST’,’TEST_DATE’,’TIMESTAMP’,NULL,NULL,NULL)@

In above procedure call we are indicating that we would like to add TEST_DATE column with data type TIMESTMAP in TEST table. Once procedure execution is successful, you can again view syscat.columns to make sure that new column is added. If you re-execute this procedure, it won’t give any error. We can add any column in any table once stored procedure is in place. Let us add one more not null column with default value of 0. Following is the syntax.

CALL alter_table (‘TEST’,’TEST_AMT’,’NUMERIC’,’13,2′,’0′,’N’)@

Here we are taking scale and precision both value in single varchar argument. As we have mentioned earlier, this is very basic procedure. One can refine it further to suit his or her needs. Using same logic one can also check for triggers, tables, procedures etc.

Sorry, the comment form is closed at this time.

 
%d bloggers like this: