sysadmin cdb
Administers the net-change database in real-time loading (RTL) replication to SAP IQ and high volume adaptive replication (HVAR) into Adaptive Server.
To hold, inspect, and release a net-change database,use:
sysadmin cdb, <q_number>, <q_type>,{hold | hold_next | unhold}
You must execute sysadmin cdb with hold or hold next before you can use sysadmin cdb to display net-change database information if the Data Server Interface Executor (DSI/E) thread is actively processing transactions.
To display all information on the net-change database, or only information on specific tracking tables, use:
sysadmin cdb, [<q_number>[,<q_type>][list[,[“<table_owner>.]<table_name>”] | [[dump_i | dump_d | dump_u | dump_nc],<table_name>] |dump_nc]]
Instructs DSI/E to suspend the current net-change database instances so that you can inspect them.
Instructs DSI/E to commit the first transaction that is ready to be committed, release the database instance, then retain the next transaction.
Instructs DSI/E to release all net-change database instances that DSI is currently retaining and resume normal DSI/E activity.
Identifies the outbound DSI stable queue for the replicate database. Examine the output of admin who, sqm command to identify the queue number.
Identifies the stable queue type, where 0 is for an outbound queue and 1 is for an inbound queue. Default is 0. If you do not specify <q_type>, the default value is used.
Specifies the replicate table name.
Displays information about the net-change database. If you do not specify the table name, list displays all instances of the outbound DSI stable queue you specify with <q_number>. Specify the table to show only the contents of that table
Returns a result containing all the columns and rows in the in-memory <Insert_Table> table.
Returns a result containing all the columns and rows in the in-memory <Update_Table> table.
Returns a result containing all the columns and rows in the in-memory <Delete_Table> table.
Returns a result containing the noncompilable commands that will be applied to the replicate table. For inserts, all the columns are returned. For deletes, only the primary keys are returned. For updates, only the primary key and updated columns are returned.
Instruct DSI/E to suspend a net-change database for inspection, after the database is populated fully. If DSI/E is not actively processing transactions, the command to hold takes effect the next time the net-change database is created and populated. Replication Server suspends the DSI/E after the net-change database is created and populated, and before the net-change database content can be applied to the replicate database. For example to suspend the current net-change database:
sysadmin cdb,101,hold
List active DSI Executor theads and the corresponding status including information on any net-change database Replication Server is processing currently:
sysadmin cdb
Output shows the RTL status for the two data servers and the respective database, queue number, and queue type for active DSI Executor (DSI/E) threads:
DSName DBName Queue QType Compile Hold CdbName Commands_in_Group ------- -------- ----- ----- ------- ---- ------- ----------------- IQSRVR2 asiqdemo 105 0 On No 0 IQSRVR iqdemo 104 0 On No 0
The status columns are:
Compile – status is “On” if RTL is active
Hold – status is “Yes” if you executed sysadmin cdb with a hold for the same <q_number> and <q_type> to hold a specific DSI/E
CdbName – the internal name of the net-change database the Replication Server is currently processing or that is in the “hold” state on that DSI/E thread. In this example, Replication Server is not processing any net-change database currently.
Commands_in_Group – the number of commands that Replication Server is compiling as a group. In this example, no command are being processed.
You do not need to suspend the DSI/E by setting it to the hold or hold_next state before you list the information on a specific DSI/E thread. Since the DSI/E is not in a hold or hold_next state, any value may change for successive executions of the command, except for the values under the Queue and QType columns:
sysadmin cdb,107,1
Output:
Queue QType CdbName TargetDB Compilable_Tables ----- ----- ---------------- ----------- ----------------- 107 1 asiqdemo_ws_46_3 asiqdemo_ws 1 Non_Compilable_Tables Commands_in_Group Compiled_Rows Non_Compilable Commands --------------------- ----------------- ------------- ------- 0 3 2 0
Display information on a net-change database that DSI/E is running currently:
NoteBefore you list information on a net-change database that DSI/E is running currently, you must suspend the database with the “hold” state.
Output is:sysadmin cdb,107,1,hold go sysadmin cdb,107,1,list go
CdbName Replicate_Table Status Cmd_Convert ---------------- ---------------------- ---------- asiqdemo_ws_46_3 dbo.test_alltypes_ws_1 compilable i2di AutoCorrection Nb_Columns PK_Cols CdbTable -------------- ---------- ------- --------------------- No 25 22 test_allpes_ws_1_46_1 Insert_Table Inserts Update_Table Updates ------------------------- ------- ------------ rs_itest_allpes_ws_1_46_1 1 rs_utest_allpes_ws_1_46_1 0 Delete_Table Deletes Non_Compilable_Cmds ------------------------ ------- ------------------- rs_dtest_allpes_ws_1_46_1 1 0 Update_Worktable Delete_Worktable -------------------------- ---------------- #rs_dtest_allpes_ws_1_46_1 Reduced_Inserts Reduced_Updates Reduced_Deletes --------------- --------------- --------------- 0 0 0 (1 rows affected)
The columns are:
CdbName – the internal name of the net-change database the Replication Server is currently processing or that is in the “hold” state on that DSI/E thread.
Replicate_Table – replicate table name
Status – “compilable” or “noncompilable” table
Cmd_Convert – command conversions applied, such none, ud2i, i2di, or i2none
AutoCorrection – whether autocorrection is applied
Nb_Columns – number of columns in the net-change database table
PK_Cols – number of primary key columns in the net-change database table
CdbTable – unique name of net-change database table
Insert_Table – name of in-memory table for insert operations in the net-change database
Inserts – number of inserts
Update_Table – name of in-memory table for update operations in the net-change database
Updates – number of updates
Delete_Table – name of in-memory table for delete operations in the net-change database
Deletes – number of deletes
Non_Compilable_Cmds – number of noncompilable commands.
Update_Worktable – name of the worktable created on the replicate data server when applying updates. This worktable is populated and joined with the replicate table
Delete_Worktable – name of the worktable created on replicate data server when applying deletes. This worktable is populated and joined with with the replicate table
Reduced_Inserts – number of inserts reduced due to compilation
Reduced_Updates – number of updates reduced due to compilation
Reduced_Deletes – number of deletes reduced due to compilation
You can list detailed information on a specific table in the net-change database by including the dump_i, dump_u, dump_d, or dump_nc options in your query to return information in the table. The options are SQL select statements executed on the net-change table.
For example to display the content of <dbo.test_alltypes_msa_1> and the <Insert_Table> in-memory table:
sysadmin cdb,106,0,dump_i,dbo.test_alltypes_msa_1
If replication is successful, this is the output:
c1 c2 c3 --------- --------- --------- 4 v ddd 3 upd qqq (2 rows affected)
To display all noncompilable commands:
sysadmin cdb,105,1,dump_nc
The output is:
Cmd Text ------ ----------------------------------------------------- 1 insert into dbo.test_alltypes_msa_1 (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20, c21, c22, c23, c24, c25) values (2, 1, 1, 'abcdefg', 'fghijkl', ‘20091125 08:50:42:100’, ‘20091125 08:51:00:000’, 1001.0000, 10, 10, 0.555544443333222211110000111122223333, 0x01234567, ‘20091125’, 1, 254.0000, 4967295, 65500, 92233720, 922337203, ‘08:50:42:113’, ‘mnopqrst’, ‘abcdefghijklmnopqrtsuvwxyz jjjjjzzzzzzzzjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjz 0 dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd dddddddddddddddddddd e ddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd ddddddddddddddddddd f’, ‘uvwxy’, convert(varchar,0x6100640064006400640064006400640064006400640064 0064006400640064006400640064006400640064006400640064006400640064 00640064006400640064006400640064006400640064006400640064006400640 06400640064006400640064006400640064006400640064006400640064006400 64006400640064006400640064006400640064006400640064006400640064006 4006400640064006400640064006400640020006500200064006400640064006 4006400640064006400640064006400640064006400640064006400640064006 4006400640064006400640064006400640064006400640064006400640064006 4006400640064006400640064006400640064006400640064006400640064006 40064006400640064006400640020006600), 0x48656c6c6f20) 2 update article set price = price*1.015 where id = 108 3 delete from dbo.test_alltypes_msa_1 where c1=1 and c2=1 and c3=1 and c4=‘ABCDEFG’ and c5=‘FGHIJKL’ and c6=‘20091125 8:50:42:100’ and c7=‘20091125 08:51:00:000’ and c8=1001.0000 and c9=10 and c10=10 and c11=0.555544443333222211110000111122223333 and c12=0x01234567 and c13= ‘20091125’ and c14=1 and c15=254.0000 and c16=4967295 and c17=65500 and c18=92233720 and c19=922337203 and c20=‘08:50:42:113’ and c21= ‘MNOPQRST’ and c23=‘UVWXY’
To display detailed information on a specific table within the net-change database:
sysadmin cdb,107,1,hold go sysadmin cdb,107,1,list,test_alltypes_ws_1 go
The output displays information that includes:
The status of operations and names of in-memory tables:
CdbName Replicate_Table Status Cmd_Convert ---------------- ---------------------- ---------- ----------- asiqdemo_ws_46_3 dbo.test_alltypes_ws_1 compilable i2di AutoCorrection Nb_Columns PK_Cols CdbTable -------------- ---------- ------- --------------------- No 25 22 test_allpes_ws_1_46_1 Insert_Table Inserts Update_Table Updates ------------------------- ------- ------------------------- ------- rs_itest_allpes_ws_1_46_1 1 rs_utest_allpes_ws_1_46_1 0 Delete_Table Deletes Non_Compilable_Cmds ------------------------ ------- ------------------- rs_dtest_allpes_ws_1_46_1 1 0 Update_Worktable Delete_Worktable -------------------------- ---------------- #rs_dtest_allpes_ws_1_46_1 Reduced_Inserts Reduced_Updates Reduced_Deletes --------------- --------------- --------------- 0 0 0 (1 row affected)
Information on all columns in the table:
Colname Coltype Maxlength Cdbtype Cdbvtype Primary_key Changed HasNull ------- ------- --------- ------- -------- ----------- ------- ------- c1 int 4 8 8 1 1 0 ... c8 money 10 1 0 1 1 0 ...c25 image 5 5 19 0 1 1 (25 rows affected)
You can list detailed information on a specific in-memory table in the net-change database by including one of these SQL commands in your query. The in-memory tables are for internal processing and the contents are not disk-resident.
You must execute sysadmin net_change_db hold or sysadmin net_change_db hold next before you can use sysadmin net_change_db list to display net-change database information.