Worth a look: The small marriage without DB2's FAQ
1, DB2 command line processor in the description of command options
Environmental Product: DB2 UDB
Platform: Cross Platform
Version: Version 6.1, Version 7.1
Problem describes the DB2 command line mode command option method uses and setting.
Answers in the DB2 command line processor command db2 list command options, get the following output:
Command line processor option
Back-end process wait time (seconds) (DB2BQTIME) = 1
Connected to the back of the number of retries (DB2BQTRY) = 60
Request queue wait time (seconds) (DB2RQTIME) = 5
Input queue wait time (seconds) (DB2IQTIME) = 5
Command options DB2OPTIONS) =
Option Description Current Setting
—— ——————————- ————
-A Display SQLCA OFF
Automatic implementation of ON-c
-E Display SQLCODE / SQLSTATE OFF
OFF-f Read input file
-L command recorded in historical documents in the OFF
-N to remove newline characters OFF
-O Display output ON
-P Display interactive input prompt ON
-R Save output report file OFF
-S stops when an error in the command OFF
-T Set statement termination character OFF
-V Echo current command OFF
-W Display FETCH / SELECT warning messages ON
-X does not print the column headings OFF
-Z Save all output to the output file OFF
The specific functions of these options and their default settings are:
. A display SQLCA data, the default is OFF.
. C whether the automatic implementation of SQL commands, the default is ON.
. E (c | s) display SQLCODE or SQLSTATE, the default is OFF.
. F filename command input from standard input and assigned to a file, the default is OFF.
Note: The command “db2 . L filename command recorded in historical documents, the default is OFF. . N remove the newline character, the default is OFF. . O output data and information will be sent to standard output, default is ON. . P shown in the interactive mode prompts the command line processor, default is ON. . R save the output to the specified file, the default is OFF. . S execute the batch file or interactively under the order shall cease to perform the operation when an error occurs, the default is OFF. . T Set statement termination character, the default is OFF. . V Echo current command to the standard output, default is OFF. . W Display FETCH or SELECT warning message, default is ON. . X does not print the column headers, the default is OFF. . Z file name will save all output to the output file, the default is OFF. These options are not required to order and can be used in combination. To modify the default settings, the option is set to open, just in the options before the “+”; and the option to turn off the add “-.” By setting the environment variable DB2OPTIONS, can also set these options. If the Intel system used: set DB2OPTIONS = '+ c-a'; in Unix systems use: export DB2OPTIONS = '+ c-a'. But the command line processor option to set the priority higher than DB2OPTIONS settings. Also use the command db2 update command options, the same can be achieved through the interactive input mode or the command file option is set to change the function. Such as: db2 update command options using c off 2, DB2 backup history file is corrupted Environmental Products: DB2 Platform: Cross Platform Version: DB2 5.2; DB2 6.1 DB2 backup history file corruption problem Answer it if DB2 database backup operation encountered a backup history file db2rhist.asc damaged, error code SQL2048 error, in accordance with the following steps: 1. First NODE000x/SQL000x directory find the file, and backup files db2rhist. bak, will db2rhist.asc file to other directory. 2. Re-run the backup command. This command will report the same error, but DB2 will automatically re-generated with db2rhist.bak db2rhist.asc file. 3. Rerun the backup command, it will be able to run correctly. If you follow the above steps, still the same error, then press the following: 1. First NODE000x/SQL000x directory find the file, and backup files db2rhist.bak, move the files to another directory in. 2. Re-run the backup command. This command will report the same error, but DB2 will automatically re-generate the two files. 3. Rerun the backup command, it will be able to run correctly. 3, DB2 7.2 version from the start to support the release of stored procedures that can be a database has been compiled stored procedures to extract and install the other databases on the target server where the database is no longer necessary c compiler. Release the stored procedure to answer the following steps: 1. If the database is upgraded to 7.1 version 7.2 version after the patch, please db2updv7-d database name So that DB2 can extract or install pre-compiled stored procedures; 2. In the source database, compiled stored procedures; 3. In the source database, run db2 “get routine into the file name from procedure stored procedure name” Extract stored procedures; 4. Upload files to the target server; 5. In the target server, run db2 “put routine from the file name” Installation has been compiled stored procedure. 4, DB2 database, a table row length, number of columns and rows per page limit in the table space Environment [products] DB2 UDB [Platform] Windows 9x/NT/2000, Unix, Linux [Version] 6.x/7.x Issue a DB2 database table row length, number of columns and rows per page limit in the table space Answers in the DB2 database, the length of each row of a table, the number of columns and rows per page limit in the table space are as follows: Table space page size table space Bank length (bytes) Table space limit the number of columns in the table space maximum number of rows per page 4K 4005 500 255 8K 8101 1012 255 16K 16293 1012 255 32K 32677 1012 255 Note: The table space page size is only 4K, 8K, 16K, 32K of four. 5, when compiling a complex SQL statement, there SQL0101N error, how do? Environment, DB2 V5.2; DB2 V6.1; DB2 V7.1 Question some of the SQL statement can be very complex, such as nested call trigger many more tables or triggers, such as SQL statements in the compile time, there SQL0101N error, how to deal with Answer for a complex SQL statement, in the call number of the table or trigger multiple triggers may take up a lot of system resources. When the SQL0101N error, first need to confirm that the system does not exist a recursive trigger. After the following parameters can increase the value to resolve this problem: 1) STMTHEAP 2) APPLHEAPSZ 3) PCKCACHESZ 6, how to restore the deleted table (Dropped Table Recovery) Answers on how to restore the deleted table (Dropped Table Recovery) Answer: 1. First of all, you can roll to restore the database (the database configuration parameter logretain or userexit open). 2. On to implement the Drop Table Recovery of table space (limited to regular tablespace), run: alter tablespace tablespace name dropped table recovery on 3. With the list history dropped table all for the database name be removed form the tableid (such as 0000000000006d0000020003) and the table structure of the generated statements (DDL), and the statement recorded tableid to restore. After the command to delete the table with a drop in the data can be exported when the first roll resume. 4. To restore the database, if you want to restore the deleted table, when the first roll plus recover dropped table tableid to the target directory. The directory is deleted in the table in the data export, such as export to ../NODE0000/data file. Table structure generated using the above-mentioned statement was deleted generated table, and then import command to import the data table. 7, how to change the name of the local system Environment [products] DB2 UDB [Platform] Windows 9x/NT/2000, Unix, Linux [Version] 5.x/6.x/7.x Problem in the control center (Control Center), you can see under the cataloging system in the node name. Can start the control center of the node as a local system name is not here to change. So how do you change the local system's name? Answer First of all, from the Control Center (Control Center) in the local system to know where the original node name. Then exit the control center, at the command line processor, enter the following command: 1. Db2set db2system = new system name 2. Db2 terminate 3. Db2 uncatalog node node name of the original 4. Db2 terminate Weight from the control center, you can see, the local system name has been changed. 8, how the state of the table space set to normal Environment [products] DB2 UDB [Platform] Windows 9x/NT/2000, Unix, Linux [Version] 5.x/6.x/7.x Issue quiesce is a continuous state of the lock, it belongs to a connection, when the connection fails, quiesce state still exists. How will the state of the table space set to normal? Answer remove the quiesce state: 1. To connect to the database 2. With a list tablespaces determine which tablespace is in quiesce state and and access to objects (object) ID 3. Determine the object ID corresponding table a) with the db2 “select tabname from syscat.tables where tablid = object ID” get table name b) use the db2 list history check is that the table 4. With db2 quiesce tablespaces for table table name reset to remove quiesce state 9, how to implement online backup Environment [products] DB2 UDB [Platform] Windows 9x/NT/2000, Unix, Linux [Version] 5.x/6.x/7.x Question how to implement online backup? Answer database when the log mode to establish the default is circular logging mode (Circular Log), then it is impossible to do online backup. So I hope the implementation of online backup, the first way to change archive log mode of the log (Archival Log). To sample database, we can change the sample in the control center database configuration parameter LOGRETAIN to Recovery, or the command line using db2 update db cfg for sample using LOGRETAIN on. Change this parameter, the re-connect to the database will show the database is in backup pending (BACKUP PENDING) state. At this point, need to do a backup of the database offline. In the Control Center, select the database offline backup or the command line with the db2 backup db sample implementation. Since then the database can be on-line backup. Control center can choose on-line database backup, or use the command line db2 backup db sample online implementation. Note: The use of online backup are the IMAGE file recovery, but also related to the log file. 10, how to use AIX bare equipment to build database management (DMS) table spaces Environment [products] DB2 UDB [Platform] AIX [Version] 5.x/6.x/7.x Question how to use AIX bare equipment to build database management (DMS) table space? Answer 1. Mklv command built with bare device, the syntax is: mklv-t jfs-y device name-U username-G group name name lv size of the hard disk vg For example: mklv-t jfs-y contest-U db2inst1-G db2iadm1 rootvg 10 hdisk0 2. And then create a table space 1). Connect to the database 2). Create a table space: Note that the command in quotes: db2 “create tablespace tablespace name managed by database using (device the size of raw device name and path)” For example: db2 “create tablespace tptest managed by database using (device '/ dev / rcontest' 80M)” db2 “create tablespace tptest managed by database using (device '/ dev / rc 1, How to start DB2 in Unix platform control center (Control Center) Environment [products] DB2 UDB [Platform] Unix [Version] 6.x/7.x Unix platform, the problem how to start the DB2 Control Center (Control Center) Unix platform solution and then start the DB2 Control Center about the steps needed to AIX operating system as an example: 1. Install DB2 choose to install the DB2 Control Center (Control Center); 2. To root account login, enter the xhost + command (after each restart the Unix command which must be imported to start the control center); 3. DB2 instance administrator account to log on, enter the following command a. db2jstrt b. db2cc This starts the DB2 Control Center (Control Center). # Xhost + # Su - db2inst1 $ Db2jstrt $ Db2cc db2cc using JDK118 export Java_HOME = / usr/jdk118 export PATH = / usr/jdk118/binPATH 2, how to query the results only show the top n records Environment [products] DB2 UDB [Platform] Windows 9x/NT/2000, Unix, Linux [Version] 5.x/6.x/7.x Problem in the query statement, do not want to display all the records only show the top n records the results Answer in the query statement, do not want to display all the records, only shows the top n records the results, you can use fetch first n rows only achieved with the back in the query. Staff to sample database table, for example, use the following command to get the query results to establish the first five: db2 “select backup bin conf config data eshow_sitemap.html generate.sh log maint sitemap.html svn tmp from staff fetch first 5 rows only” 3, how to see changes in the command line parameters instance and database configuration Environment [products] DB2 UDB [Platform] Windows 9x/NT/2000, Unix, Linux [Version] 5.x/6.x/7.x How they view the changes in the command line instance and database configuration parameters Answer an instance of the command line to view the configuration parameters, available db2 get dbm cfg command. Similarly, use db2 get db cfg for the database name can be a database configuration parameter. The following is part of the database parameters: Log file size (4KB) (LOGFILSIZ) = 250 Number of primary log files (LOGPRIMARY) = 3 Number of secondary log files (LOGSECOND) = 2 Modify the instance configuration parameters, use the db2 update dbm cfg using parameter name the new value Similarly, using db2 update db cfg for the database name parameter name using the new database configuration parameter value changes. For example, to modify the sample database configuration parameter LOGPRIMARY 10, using the following commands: db2 update db cfg for sample using logprimary 10 4, how to implement DB2 command line script (script) Environment [products] DB2 UDB [Platform] Windows 9x/NT/2000, Unix, Linux [Version] 5.x/6.x/7.x Question how to implement DB2 command line script (script) Answer in the command window, run the DB2 script, available db2-svtf script file name to achieve. For example, the script file named sample.sql, run: db2-svtf sample.sql Parameters: s behalf to run the script encountered an error when the suspension v representative of the output to the screen t means; number as each line separator f refers to the script file name must end with If you want to encounter an error and do not stop even operating parameters can be removed s. Recommended links: Cult3D Animation with 3DMax default works Detailed analysis: teach you to master the table character set and proofreading How to use volume shadow copy service vss for Half an hour to teach you learn regular EXPRESSIONS Utilize all practical use of small floating window all CAPTURES Thunder 5.9 Good promotion of Accounting COUPLE stores Edition AWS Express, Turned Out To Yanhuang PCC Writing A New Chapter In BPM
