DB2

Declare Temporary table cause must be followed a Begin and End, otherwise will raise a compile error.

[IBM][CLI Driver][DB2/6000] SQL0104N  在 "" 之后发现意外的标记 "<variable declaration>"。期望的标记可能包括："<SQL statement>"。  LINE NUMBER=86.  SQLSTATE=42601 

DECLARE GLOBAL TEMPORARY TABLE SESSION.t (
      procr_code        char(8)
) 
WITH REPLACE 
NOT LOGGED 
IN GLBL_TEMP_01; 
BEGIN
-- code here
END;

More about temporary tables please refer to DB2 online document(v8). Here's also an article from IBM DeveloperWorks.

valatile

• a. 反复无常的,挥发性的
  

VOLATILE CARDINALITY or NOT VOLATILE CARDINALITY
Indicates to the optimizer whether or not the cardinality of table table-name can
vary significantly at run time. Volatility applies to the number of rows in the
table, not to the table itself. CARDINALITY is an optional keyword. The default
is NOT VOLATILE.

VOLATILE
Specifies that the cardinality of table table-name can vary significantly at
run time, from empty to large. To access the table, the optimizer will use
an index scan (rather than a table scan, regardless of the statistics) if that
index is index-only (all referenced columns are in the index), or that index
is able to apply a predicate in the index scan. The list prefetch access method
will not be used to access the table. If the table is a typed table, this option
is only supported on the root table of the typed table hierarchy (SQLSTATE 428DR).

NOT VOLATILE
Specifies that the cardinality of table-name is not volatile.
Access plans to this table will continue to be based on existing statistics and
on the current optimization level.

NOTE: The keyword could be specified within ALTER TABLE, but not CREATE TABLE.

 

DB2 automatically catalogs databases when they are created. It catalogs an entry
for the database in the local database directory and another entry in the system
database directory. If the database is created from a remote client (or a client which
is executing from a different instance on the same machine), an entry is also made
in the system database directory at the client instance.

Databases on the same node as the database manager instance are cataloged as
indirect entries. Databases on other nodes are cataloged as remote entries.

List DBs in Local Database Directory

$ db2 list db directory on /path_to_where_db_created 

List DBs in System Database Directory

$ db2 list db directory

 

As we know, we could issue following command to query status for all of tablespaces in current connected DB.

$ db2 list tablespaces show detail > /tmp/ts.list

Then find the status for the tablespaces we concerned.

Is there's a way to query the status for one specified tablespace?

My answer is No. 

1. First, I don't find the related column defination in the table or view:
• sysibm.systablespaces
• syscat.tablespaces
2. Second, I don't think DB2 stores the status flag within syscata tables. The reason is that once a tablespace comes into a special status, it will not be allowed changing any longer(this may also happen on a system catalog tablespace.) that will lead to a conflict on changing the tablespace status flag if stores it within system catalog tables.

• http://publib.boulder.ibm.com/infocenter/db2luw/v8//topic/com.ibm.db2.udb.doc/admin/c0005420.htm

Reference

• http://publib.boulder.ibm.com/infocenter/db2luw/v8//topic/com.ibm.db2.udb.doc/admin/c0005418.htm 

DB2 sets tablespace as Backup Pending state after loading data into a table in linear logging database(that is, logretain or userexit is on), whatever the load is successful or failed. the reason is that the load process will not write log file. from the begin time of loading to the end time of loading, there will be a blank segment in log file, and the rollforward recovery can Not jump over the blank segment to apply the later log file.

so database needs a backup after loading to make sure db2 have recovery capicibility.

it's able to use [COPY YES|NO]  or [NONRECOVERABLE] to prevent tablespace go into Backup Pending state. COPY YES will do the backup automatically after loading, and COPY NO and NONRECOVERABLE will give up this backup that means db2 will be not able to recover the database once an serious error occured.

继续阅读 "Tablespaces are put into backup pending after a load" 的剩余内容

Problem Description 

There's no any response for creating or droping any procedures in DB2. Seems it's pending there, without any error returned. This time UPDATE statement on tables could be issued successfully.

Check Process

Check OS disk available

$ df -kl 

Check database configure

db2 => get db cfg |more 

Check system catalog tablespace 

db2 => list tablespaces show detail |more
syscatspace	0x0000 

Cause 

It caused by issuing CREATE or DROP statement without COMMIT or ROLLBACK statement in CLP which is in non-Autocommit mode.

Solution 

After you issue a COMMIT or a ROLLBACK in the CLP or close it, the pending is gone.

We must be very careful when we use client in non-Autocommit mode just like CLPs in this case.

To check the Autocommit mode in CLP issues. Refer to:

set COMMIT mode in db2 clp

db2 => list command options

 -c    自动落实                                  OFF 

More 

From this point, we could get more. Most of strange things like this(pending there and no error return) are caused by locking.

In this case, it was only the opertation on procedures being pended, so track on this thread to suppose the syscat tables related with procedures are locked then find the what probably causes it.
 

There's an object called index view since MS SQL Server 2000, it's an index which is created on an view which is based on a local table. it is attent to imporve the select performence on the view when this view is based on multi-table and has complex logic.

I try to find a simular thing in DB2, but there's no the simular solution in DB2 v8.2 for now. Instead, I found another thing instested.

DB2 allows creating index on the tables or views in remote data source, such as a database on another host or an XML data source. To be exactly, that's Not true Index, it's Index Specification. It requests that the tables in remote data source have created index in its own system. And it only repeat the index description in local system.

Local DB2 Env                                            Remote DB2 Env

Index Specification    ->    Nickname    - - ->   True Index    ->    Table

See following example in DB2 info center.

• CREATE INDEX statement
  http://publib.boulder.ibm.com/infocenter/db2luw/v8//topic/com.ibm.db2.udb.doc/admin/r0000919.htm?resultof=%22%63%72%65%61%74%65%22%20%22%63%72%65%61%74%22%20%22%69%6e%64%65%78%22%20%22%73%74%61%74%65%6d%65%6e%74%22%20
  

Example 3:  The nickname EMPLOYEE references a data source table called CURRENT_EMP. After this nickname was created, an index was defined on CURRENT_EMP. The columns chosen for the index key were WORKDEBT and JOB. Create an index specificationindex. Through this specification, the optimizer will know that the index exists and what its key is. With this information, the optimizer can improve its strategy to access the table. that describes this

   CREATE UNIQUE INDEX JOB_BY_DEPT
     ON EMPLOYEE (WORKDEPT, JOB)
     SPECIFICATION ONLY        

Note: the Nickname is only allowed to be created on tables in local database and on much kind of objects at remote data source. It's not allowed to be created on views in local database.

As default, DB2 CLP will do commit after you issue each DB2 statements or SQL automatically.

If you want to change it instead of issuing COMMIT or ROLLBACK manually, do following.

SQLLIB\BIN> db2
db2 => list command options
    -c   ON
db2 => update command options using c off
db2 => list command options
    -c    OFF

This change will only exists during this session. The setting will lose when you close this CLP and open CLP next time.

following cmd will get the same result with above.

SQLLIB\BIN> db2 list command options
    -c    ON
SQLLIB\BIN> db2 +c
db2 =>
db2 => list command options
    -c    OFF
db2 => quit
SQLLIB\BIN> db2 list command options
    -c    ON

There's also the way to keep the settings forever, do following.

SQLLIB\BIN\> db2 list command options
    -c    ON
SQLLIB\BIN\> db2set db2options=+c
SQLLIB\BIN\> db2 list command options
    -c    OFF

This change will take effection immediately even for others having been opened CLP windows. And will keep along.

Use following cmd set it back to ON. 

SQLLIB\BIN> db2set db2opptions=-c

Reference

• http://www.chinaunix.net/jh/22/164405.html 

在DB2 中重命名表或者索引

db2=> RENAME TABLE EMP TO EMPLOYEE
db2=> RENAME TABLE ABC.EMP TO EMPLOYEE 
db2=> RENAME INDEX NEW-IND TO IND
db2=> RENAME INDEX ABC.NEW-IND TO IND

拥有检查约束和非主键自增列的表不允许重命名，在视图、触发器、函数或者物化查询表中被引用的表也不允许重命名，只能Drop 掉，重新创建。

参考资源 

• http://spaces.msn.com/members/ssbx/Blog/cns!1pbteQlcj9M5ShfSFF2U45MA!151.entry
• http://spaces.msn.com/members/samm1999/Blog/cns!1pj3HDFElwIAVGqaPIMGoaPA!348.entry
• http://spaces.msn.com/members/thinkway/Blog/cns!1pbQqN3pAmqciYgNQVf99ISA!114.entry