The following is my last article.
41. Quiesce is a continuous state lock that belongs to a connection. When the connection fails, the Quiesce state still exists. How to set the status of the table space to normal?
A: Remove the Quiesce status: 1. Connect to the database
2. Judging which tableSpace is in the Quiesce status and acquisition object (Object) ID with list tablespaces
3. Judgment the table corresponding to the object ID
Get the table name with DB2 "SELECT TabName from Syscat.tables where Tablid = Object ID"
b) Judging the DB2 List History is that table
4. Use the DB2 Quiesce tablespaces for Table Table name RESET to remove the Quiesce status
42. How to create a user-defined temporary table in DB2 UDB?
A: You can use the DECLARE GLOBAL TEMPORARY TABLE statement to define a temporary table. This statement needs to be used in the application. The user-defined temporary table is continuous only before the application is disconnected from the database. The description of this table does not appear in the system directory, making it not continuing for other applications, nor can you share this table with other applications. When the application is used in this table to terminate or disconnects to the database, the data in this table is deleted, which is implicitly removed. Below is an example of defining a temporary table: Declare Global Temporary Table GBL_TEMP LIKE EMPLTABL ON COMMIT DELETE ROWS NOT Logged In USR_TBSP This statement creates a user-temporary table named GBL_TEMP. Define the names of the columns used by this user temporary table and the names and descriptions of the columns of EMPLTABL are identical. Implicit definitions include only column names, data types, can be empty features and list default value attributes. All other column properties are not defined, including unique constraints, external keyword constraints, triggers, and indexes. When performing a CommIT operation, if the WITH HOLD cursor is not opened to the table, all the data in the table is deleted. The changes to the user temporary table are not recorded. The user temporary table is placed in the specified user temporary table space. This table space must exist, otherwise the declaration of this table will fail.
43. How to avoid the log space error when using the import command to enter the data to the database?
A: When executing the import command, if you use a loop log, you sometimes have a log full error. At this time, you can solve it. Because the log space is often because all the logs are active. And the commit executes, It will release the resources occupied, including the log. This way, the log used by the current transaction is executed after the commit command is executed, that is, it becomes an inactive state.
44. How to check the database connection related to the database?
A: Take the TCP / IP connection as an example. If the connection fails, you can check the following items: a) On the server: 1) The DB2COMM registry value includes a value TCPIP. Enter the DB2SET DB2COMM command to check the settings of the DB2COMM registry value. For details, please refer to the Administration Guide. 2) Update the Services file correctly. 3) The service name (SVCENAME) parameter is correctly updated in the database management program profile. 4) The security service has started. Enter the NET Start DB2NTSECSERVER command (only for Windows NT servers). 5) Create and catalog the database correctly. 6) The database manager has stopped and started again (enter the DB2Stop and DB2Start command on the server). * If there is a problem when you start a protocol's connection management program, warning information will appear, and the error message is recorded in the db2diag.log file. B) On the client: 1) Update Services and Hosts files (if used) correctly. 2) This node catches this node using the correct hostname or IP address (IP_ADDress). 3) The port number must match, or the service name must be mapped to the port number used on the server. 4) The node name (Node_Name) specified in the database directory points to the correct project in the node directory. 5) The database has been correctly catacled. It uses the server's database alias (Database_alias) that is used when creating the database on the server, as the database name (Database_name) on the client. After verifying these items, if the connection fails, refer to DB2 Troubleshooting Guide45. A way to meet the transaction log when you can bypass the entire table
A: With empty files to import (IMPORT) and replace (Replace) Target Table and Delete (delete) table operation
When deleting the data in the entire table with the Delete Table command, the operation will delete the records recorded in the table one by one, and record the transaction log of the event. When the amount of data in the table is large, if the active transaction log is not large enough, it will encounter a transaction log that is full, and roll back the log. Even if the active transaction log is large enough, the operation of deleting a large amount of data will also take a lot of time. This problem can solve this problem with an empty file import (import) and replan with a replace. For example, the import from / dev / null of del replace INTO target table name This transaction log will only record the command, and immediately release the space, and not scan records as you delete the command, which is similar to DROP. The table creates a table that is exactly the same as only the data. For a table belonging to the DMS table space, the delete command scans the record one by one, and the recorded space is still marked as the table, and does not release the space immediately, you need to use the REORG command to release the remaining space. Use the List TableSpace Show Detail to compare the size of the remaining space in the postsheet space in the two commands. With the load command plus the replace parameter can achieve the effect similar to the import command plus Replace, but because the Load itself does not remember the log, for the recoverable database, the LOAD is recommended to do online backup, in comparison, the import command plus Replace is more simple in operation. 46. How to get the form structure and an index information
A: You can use the "Describe" command: (1) Display SQLDA information about the SELECT statement; (2) The column information of the table or view; (3) Index information of the watch or view; example: 1) The following example Used to describe the SELECT statement: DB2 "Describe Select * from staff" SQLDA Information SqldAid: SQLDA SQLDABC: 896 SQLDABC: 896 SQLN: 20 SQLD: 7 Column Information Sqltype Sqllen Sqlname.Data Sqlname.length -------------------------------------------- ---------------------- --------------- 500 Smallint 2 ID 2 449 VARCHAR 9 Name 4 501 Smallint 2 DEPT 4
2) The following example is used to describe the table structure: DB2 Describe Table User1.Department Table: User1.Department Column Name Type Schema Type Name Length Scale Nulls ---------------- -------- -------- ---------- Area Sysibm Smallint 2 0 No Dept Sysibm Character 3 0 No Deptname Sysibm Character 20 0 YES
3) The following example is used to describe the index structure: DB2 DESCRIBE INDEXES for Table User1.Department Table: User1.Department Index Schema Index Name Unique Rule Number of Column ------------ ----- ------------------------------ User1 IDX1 U 2
47. How to start the DB2 Control Center on UNIX platform (Control Center)
A: The DB2 Control Center is started on the UNIX platform. You need to take a few steps to use the AIX operating system as an example: 1. Select the DB2 Control Center when installing DB2; 2. Log in with the root account, enter the Xhost command ( You need to enter this command to start the control center each time you restart UNIX; #XHOST #SU - DB2Inst1 $ db2jstrt $ db2cc db2cc Used JDK118 Export Java_Home = / usr / jdk118 export path = / usr / jdk118 / bin: $ PATH
48. How to change the local system name
A: First, you know the original node name where the local system is located from the Control Center. Then exit the control center, enter the following command in the command line processor: 1. DB2SET DB2SYSTEM = New System Name 2. DB2 Terminate 3. DB2 Uncatalog Node Original Name 4. DB2 Terminate reconciliation control center, you can see, local system Name has been changed.
Declare undo handler for sqlexception
Set errmsg = 'error, Rollback Was Issued'; 49.db2signal and resignal usage?
A: Example
CREATE Procedure XHzq_db.g10 ()
Language SQL
Begin
Declare C1 Condition for SQLSTATE '38001';
Declare Continue Handler for C1
INSERT INTO XHZQ_DB.ZZ_RESULT (PROC, RES) VALUES ('EXEC OF G10', 'EXIT HANDLER FIRED');
INSERT INTO XHZQ_DB.ZZ_RESULT (PROC, RES) VALUES ('Exec Of G10', 'Start of Proc');
Signal SQLSTATE '38001';
INSERT INTO XHZQ_DB.ZZ_RESULT (PROC, RES) VALUES ('EXEC OF G10', 'End of Proc');
END;
If the call is called, select * from xhzq_db.zz_result;
The results are as follows:
Proc Res
EXEC OF G10 Start of Proc
EXEC OF G10 Exit Handler Fired
EXEC OF G10 End of Proc
Example
CREATE Procedure G11 ()
Language SQL
Begin
Declare C1 Condition for SQLSTATE '38001';
INSERT INTO RESULT (PROC, RES) VALUES ('Exec of G11', 'Start of Proc');
Signal SqlState '38001'; / * The Handler Will Be Fired by this statement * /
INSERT INTO RESULT (PROC, RES) VALUES ('EXEC OF G11', 'End of Proc');
END;
After the call, SELECT *.
The results are as follows:
Proc Res
EXEC OF G11 Start of Proc
49. Regarding the problem of storing Chinese in AS400DB2
A: AS400 DB2 storage in Chinese is specially processed.
The format is a 0e start Chinese string 0f end, if there is a string mixed with Chinese and English string, each Chinese string adds 0E and 0F
For example: China Storage 0e China 0F
AV China Manufacturing KK Hunan Post-storage: AV0E China Manufacturing 0FKK0E Hunan 0F
Note: In addition to characters other than A-Z, A-Z are also processed by Chinese strings.
50. SQLCode bundle statement
answer:
Declare Sqlcode Integer Default 0;
Declare SqlState Char (5) Default '00000';
Declare continue handler for sqlexception set retcode = SQLCODE;
Declare Continue Handler for Sqlwarning Set Retcode = Sqlcode;
Declare Continue Handler for Not Found Set Retcode = Sqlcode; I have completed my content, I hope to be interested in friends! Can contact me!
QQ: 50839655
MSN: DEKKERDILLON@hotmail.com
Email: zhangtao @ xhzq.com, dekker99 @ 163.com
HTMP: //www.xhzq.com
