How to look up for a object in DB2 and know what kind of object it is. It is called like this: db2 "call LOOK_FOR_OBJECT('NULLID','SQLC2H21')"

Look_For_Object.sql

CREATE OR REPLACE PROCEDURE LOOK_FOR_OBJECT (
IN SCHEM VARCHAR(256),
IN NAME VARCHAR(256)
)
LANGUAGE SQL
SPECIFIC PRO_LOOK_FOR_OBJECT
DYNAMIC RESULT SETS 1
READS SQL DATA
DETERMINISTIC
NO EXTERNAL ACTION
PARAMETER CCSID UNICODE
P_LOOK_FOR: BEGIN
DECLARE STMT VARCHAR(10000);
DECLARE REF CURSOR
WITH RETURN TO CALLER
FOR RS;
IF (SCHEM IS NULL) THEN
SET SCHEM = '';
END IF;
SET STMT = 'SELECT ''TABLE'' AS TYPE, '
|| 'VARCHAR(SUBSTR(T.TABSCHEMA, 1, 32), 32) AS SCHEMA, '
|| 'VARCHAR(SUBSTR(T.TABNAME, 1, 32), 32) AS NAME '
|| 'FROM SYSCAT.TABLES T '
|| 'WHERE T.TABSCHEMA LIKE ''%' || SCHEM || '%'' '
|| 'AND T.TABNAME LIKE ''%' || NAME || '%'' '
|| 'UNION ALL '
|| 'SELECT ''FUNCTION'' AS TYPE, '
|| 'VARCHAR(SUBSTR(F.FUNCSCHEMA, 1, 32), 32) AS SCHEMA, '
|| 'VARCHAR(SUBSTR(F.FUNCNAME, 1, 32), 32) AS NAME '
|| 'FROM SYSCAT.FUNCTIONS F '
|| 'WHERE F.FUNCSCHEMA LIKE ''%' || SCHEM || '%'' '
|| 'AND F.FUNCNAME LIKE ''%' || NAME || '%'' '
|| 'UNION ALL '
|| 'SELECT ''INDEX'' AS TYPE, '
|| 'VARCHAR(SUBSTR(I.INDSCHEMA, 1, 32), 32) AS SCHEMA, '
|| 'VARCHAR(SUBSTR(I.INDNAME, 1, 32), 32) AS NAME '
|| 'FROM SYSCAT.INDEXES I '
|| 'WHERE I.INDSCHEMA LIKE ''%' || SCHEM || '%'' '
|| 'AND I.INDNAME LIKE ''%' || NAME || '%'' '
|| 'UNION ALL '
|| 'SELECT ''MODULE'' AS TYPE, '
|| 'VARCHAR(SUBSTR(M.MODULESCHEMA, 1, 32), 32) AS SCHEMA, '
|| 'VARCHAR(SUBSTR(M.MODULENAME, 1, 32), 32) AS NAME '
|| 'FROM SYSCAT.MODULES M '
|| 'WHERE M.MODULESCHEMA LIKE ''%' || SCHEM || '%'' '
|| 'AND M.MODULENAME LIKE ''%' || NAME || '%'' '
|| 'UNION ALL '
|| 'SELECT ''PACKAGE'' AS TYPE, '
|| 'VARCHAR(SUBSTR(P.PKGSCHEMA, 1, 32), 32) AS SCHEMA, '
|| 'VARCHAR(SUBSTR(P.PKGNAME, 1, 32), 32) AS NAME '
|| 'FROM SYSCAT.PACKAGES P '
|| 'WHERE P.PKGSCHEMA LIKE ''%' || SCHEM || '%'' '
|| 'AND P.PKGNAME LIKE ''%' || NAME || '%'' '
|| 'UNION ALL '
|| 'SELECT ''PROCEDURE'' AS TYPE, '
|| 'VARCHAR(SUBSTR(SP.PROCSCHEMA, 1, 32), 32) AS SCHEMA, '
|| 'VARCHAR(SUBSTR(SP.PROCNAME, 1, 32), 32) AS NAME '
|| 'FROM SYSCAT.PROCEDURES SP '
|| 'WHERE SP.PROCSCHEMA LIKE ''%' || SCHEM || '%'' '
|| 'AND SP.PROCNAME LIKE ''%' || NAME || '%'' '
|| 'UNION ALL '
|| 'SELECT ''SEQUENCE'' AS TYPE, '
|| 'VARCHAR(SUBSTR(S.SEQSCHEMA, 1, 32), 32) AS SCHEMA, '
|| 'VARCHAR(SUBSTR(S.SEQNAME, 1, 32), 32) AS NAME '
|| 'FROM SYSCAT.SEQUENCES S '
|| 'WHERE S.SEQSCHEMA LIKE ''%' || SCHEM || '%'' '
|| 'AND S.SEQNAME LIKE ''%' || NAME || '%'' '
;
 
PREPARE RS FROM STMT;
OPEN REF;
END P_LOOK_FOR@

The following is a bash version of the previous function. This does not need to create an object in the database. It could executed as a script, or in copy / paste directly in the terminal.

You need to pass one or two parameters: The name of the object OR the schema and the name of the object.

#!/bin/bash

OBJECT=$2
if [[ -n ${OBJECT} ]] ; then
  SCHEMA=$1
else
  OBJECT=$1
fi

STMT=""
if [[ -n ${SCHEMA} ]] ; then
 STMT="${STMT} SELECT 'TABLE' AS TYPE, "
 STMT="${STMT} VARCHAR(SUBSTR(T.TABSCHEMA, 1, 32), 32) AS SCHEMA, "
 STMT="${STMT} VARCHAR(SUBSTR(T.TABNAME, 1, 32), 32) AS NAME "
 STMT="${STMT} FROM SYSCAT.TABLES T "
 STMT="${STMT} WHERE T.TABSCHEMA LIKE '%${SCHEMA}%' "
 STMT="${STMT} AND T.TABNAME LIKE '%${OBJECT}%' "
 STMT="${STMT} UNION ALL "
 STMT="${STMT} SELECT 'FUNCTION' AS TYPE, "
 STMT="${STMT} VARCHAR(SUBSTR(F.FUNCSCHEMA, 1, 32), 32) AS SCHEMA, "
 STMT="${STMT} VARCHAR(SUBSTR(F.FUNCNAME, 1, 32), 32) AS NAME "
 STMT="${STMT} FROM SYSCAT.FUNCTIONS F "
 STMT="${STMT} WHERE F.FUNCSCHEMA LIKE '%${SCHEMA}%' "
 STMT="${STMT} AND F.FUNCNAME LIKE '%${OBJECT}%' "
 STMT="${STMT} UNION ALL "
 STMT="${STMT} SELECT 'INDEX' AS TYPE, "
 STMT="${STMT} VARCHAR(SUBSTR(I.INDSCHEMA, 1, 32), 32) AS SCHEMA, "
 STMT="${STMT} VARCHAR(SUBSTR(I.INDNAME, 1, 32), 32) AS NAME "
 STMT="${STMT} FROM SYSCAT.INDEXES I "
 STMT="${STMT} WHERE I.INDSCHEMA LIKE '%${SCHEMA}%' "
 STMT="${STMT} AND I.INDNAME LIKE '%${OBJECT}%' "
 STMT="${STMT} UNION ALL "
 STMT="${STMT} SELECT 'MODULE' AS TYPE, "
 STMT="${STMT} VARCHAR(SUBSTR(M.MODULESCHEMA, 1, 32), 32) AS SCHEMA, "
 STMT="${STMT} VARCHAR(SUBSTR(M.MODULENAME, 1, 32), 32) AS NAME "
 STMT="${STMT} FROM SYSCAT.MODULES M "
 STMT="${STMT} WHERE M.MODULESCHEMA LIKE '%${SCHEMA}%' "
 STMT="${STMT} AND M.MODULENAME LIKE '%${OBJECT}%' "
 STMT="${STMT} UNION ALL "
 STMT="${STMT} SELECT 'PACKAGE' AS TYPE, "
 STMT="${STMT} VARCHAR(SUBSTR(P.PKGSCHEMA, 1, 32), 32) AS SCHEMA, "
 STMT="${STMT} VARCHAR(SUBSTR(P.PKGNAME, 1, 32), 32) AS NAME "
 STMT="${STMT} FROM SYSCAT.PACKAGES P "
 STMT="${STMT} WHERE P.PKGSCHEMA LIKE '%${SCHEMA}%' "
 STMT="${STMT} AND P.PKGNAME LIKE '%${OBJECT}%' "
 STMT="${STMT} UNION ALL "
 STMT="${STMT} SELECT 'PROCEDURE' AS TYPE, "
 STMT="${STMT} VARCHAR(SUBSTR(SP.PROCSCHEMA, 1, 32), 32) AS SCHEMA, "
 STMT="${STMT} VARCHAR(SUBSTR(SP.PROCNAME, 1, 32), 32) AS NAME "
 STMT="${STMT} FROM SYSCAT.PROCEDURES SP "
 STMT="${STMT} WHERE SP.PROCSCHEMA LIKE '%${SCHEMA}%' "
 STMT="${STMT} AND SP.PROCNAME LIKE '%${OBJECT}%' "
 STMT="${STMT} UNION ALL "
 STMT="${STMT} SELECT 'SEQUENCE' AS TYPE, "
 STMT="${STMT} VARCHAR(SUBSTR(S.SEQSCHEMA, 1, 32), 32) AS SCHEMA, "
 STMT="${STMT} VARCHAR(SUBSTR(S.SEQNAME, 1, 32), 32) AS NAME "
 STMT="${STMT} FROM SYSCAT.SEQUENCES S "
 STMT="${STMT} WHERE S.SEQSCHEMA LIKE '%${SCHEMA}%' "
 STMT="${STMT} AND S.SEQNAME LIKE '%${OBJECT}%' "
else
 STMT="${STMT} SELECT 'TABLE' AS TYPE, "
 STMT="${STMT} VARCHAR(SUBSTR(T.TABSCHEMA, 1, 32), 32) AS SCHEMA, "
 STMT="${STMT} VARCHAR(SUBSTR(T.TABNAME, 1, 32), 32) AS NAME "
 STMT="${STMT} FROM SYSCAT.TABLES T "
 STMT="${STMT} WHERE T.TABNAME LIKE '%${OBJECT}%' "
 STMT="${STMT} UNION ALL "
 STMT="${STMT} SELECT 'FUNCTION' AS TYPE, "
 STMT="${STMT} VARCHAR(SUBSTR(F.FUNCSCHEMA, 1, 32), 32) AS SCHEMA, "
 STMT="${STMT} VARCHAR(SUBSTR(F.FUNCNAME, 1, 32), 32) AS NAME "
 STMT="${STMT} FROM SYSCAT.FUNCTIONS F "
 STMT="${STMT} WHERE F.FUNCNAME LIKE '%${OBJECT}%' "
 STMT="${STMT} UNION ALL "
 STMT="${STMT} SELECT 'INDEX' AS TYPE, "
 STMT="${STMT} VARCHAR(SUBSTR(I.INDSCHEMA, 1, 32), 32) AS SCHEMA, "
 STMT="${STMT} VARCHAR(SUBSTR(I.INDNAME, 1, 32), 32) AS NAME "
 STMT="${STMT} FROM SYSCAT.INDEXES I "
 STMT="${STMT} WHERE I.INDNAME LIKE '%${OBJECT}%' "
 STMT="${STMT} UNION ALL "
 STMT="${STMT} SELECT 'MODULE' AS TYPE, "
 STMT="${STMT} VARCHAR(SUBSTR(M.MODULESCHEMA, 1, 32), 32) AS SCHEMA, "
 STMT="${STMT} VARCHAR(SUBSTR(M.MODULENAME, 1, 32), 32) AS NAME "
 STMT="${STMT} FROM SYSCAT.MODULES M "
 STMT="${STMT} WHERE M.MODULENAME LIKE '%${OBJECT}%' "
 STMT="${STMT} UNION ALL "
 STMT="${STMT} SELECT 'PACKAGE' AS TYPE, "
 STMT="${STMT} VARCHAR(SUBSTR(P.PKGSCHEMA, 1, 32), 32) AS SCHEMA, "
 STMT="${STMT} VARCHAR(SUBSTR(P.PKGNAME, 1, 32), 32) AS NAME "
 STMT="${STMT} FROM SYSCAT.PACKAGES P "
 STMT="${STMT} WHERE P.PKGNAME LIKE '%${OBJECT}%' "
 STMT="${STMT} UNION ALL "
 STMT="${STMT} SELECT 'PROCEDURE' AS TYPE, "
 STMT="${STMT} VARCHAR(SUBSTR(SP.PROCSCHEMA, 1, 32), 32) AS SCHEMA, "
 STMT="${STMT} VARCHAR(SUBSTR(SP.PROCNAME, 1, 32), 32) AS NAME "
 STMT="${STMT} FROM SYSCAT.PROCEDURES SP "
 STMT="${STMT} WHERE SP.PROCNAME LIKE '%${OBJECT}%' "
 STMT="${STMT} UNION ALL "
 STMT="${STMT} SELECT 'SEQUENCE' AS TYPE, "
 STMT="${STMT} VARCHAR(SUBSTR(S.SEQSCHEMA, 1, 32), 32) AS SCHEMA, "
 STMT="${STMT} VARCHAR(SUBSTR(S.SEQNAME, 1, 32), 32) AS NAME "
 STMT="${STMT} FROM SYSCAT.SEQUENCES S "
 STMT="${STMT} WHERE S.SEQNAME LIKE '%${OBJECT}%' "
fi

echo ${STMT}
db2 ${STMT}