Useful Selects


Problem:
When trying to insert a values into a table a exception is raised on inserting a null value in a column. The problem is db2 does not tell you the column name, but gives you column number and table id. Below is a select I use alot in determining what the column is.
Solution:
select tabname, colname from syscat.columns where colno=9 and tabname=(select tabname from syscat.tables where tbspaceid=2 and tableid=78)



Problem:
How to determine without any monitoring tools, as I am not a DBA and does not have access to it, what queries is running and if there is a block.
Solution:

SELECT * FROM SYSIBMADM.LOCKS_HELD l WHERE AGENT_ID = ???
SELECT * FROM SYSIBMADM.LOCKWAITS l
SELECT * FROM SYSIBMADM.LONG_RUNNING_SQL l WHERE AGENT_ID = ???
SELECT * FROM SYSIBMADM.MON_CURRENT_SQL m
SELECT * FROM SYSIBMADM.MON_CURRENT_UOW m
SELECT * FROM SYSIBMADM.MON_WORKLOAD_SUMMARY m

Problem:
Got the error:

SQL1188N  Column "9" of the SELECT or VALUES statement is not compatible with
table column "9". The source column has sqltype "448", and the target column
has sqltype "496".

Solution:
1)  Get the column name with the select...

select tabname, colname, colno
from syscat.columns where  tabname= 'TABLE'

NOTE!!! I discovered that the Column "9" is the actual number of the column that is being loaded in the cursor.

2) Found the meanings of the codes @ http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.db2.luw.sql.ref.doc%2Fdoc%2Fr0001030.


Below is a select to enable and disable foreign keys:

ENABLE:

SELECT 'ALTER TABLE SSMD00.'||RTRIM(TABNAME)||' ALTER FOREIGN KEY '||RTRIM(CONSTNAME)||' ENFORCED;' 
from syscat.references 
where REFTABNAME IN ('TABLE') 
ORDER BY REFTABNAME;

DISABLE:

SELECT 'ALTER TABLE SSMD00.'||RTRIM(TABNAME)||' ALTER FOREIGN KEY '||RTRIM(CONSTNAME)||' NOT ENFORCED;' 
from syscat.references 
where REFTABNAME IN ('TABLE') 
ORDER BY REFTABNAME

1 comment:

  1. Getting size of tables in DB2:

    Data size:
    select char(date(t.stats_time))||' '||char(time(t.stats_time)) as
    statstime
    ,substr(t.tabschema,1,4)||'.'||substr(t.tabnam e,1,24) as tabname
    , card as rows_per_table
    , decimal(float(t.npages)/ ( 1024 / (b.pagesize/1024)),9,2) as used_mb
    , decimal(float(t.fpages)/ ( 1024 / (b.pagesize/1024)),9,2) as
    allocated_mb
    from syscat.tables t
    , syscat.tablespaces b
    where t.tbspace=b.tbspace
    with ur;

    index size:

    select rtrim(substr(i.tabschema,1,8))||'.'||rtrim(substr( i.tabname,
    1,24)) as tabname
    , decimal(sum(i.nleaf)/( 1024 / (b.pagesize/1024)),12,2) as
    indx_used_pertable
    from syscat.indexes i, syscat.tables t
    , syscat.tablespaces b
    where i.tabschema is not null
    and i.tabname=t.tabname and i.tabschema=t.tabschema and
    t.tbspace=b.tbspace
    group by i.tabname,i.tabschema, b.pagesize with ur;

    You can verify the result against the 'Estimate Size' of db2 control
    center--> tables--> your_table-->Estimate Size.

    ReplyDelete