Administers the net-change database in real-time loading (RTL) replication to Sybase 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}
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]]
sysadmin cdb,101,hold
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.
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
sysadmin cdb,107,1,hold go sysadmin cdb,107,1,list goOutput is:
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
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)
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’
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.