Problem With Resume Connection

[Former Member]

I Have Problem with Sybase Replication because table has Column With Name "View"  and it's make problem while Statement ,

i will make table definition with quoted

the Problem it was about 850 transaction

and using resume connection with skip and i already skipped about 450 transaction  , every transaction i skipped was sent to exception until now there is no problem

but now resume connection with skip didn't send any transaction to exception and it's do nothing

inform that i have enough space at all servers RSSD and Main and Replication server

, can i modify statement in Queue ir delete cretin transaction for queue  

[former_member285566]

Andrew,

There is a command called "sysadmin sqm_zap_command" to delete a transaction command out of queue.

1. Dump the queue

sysadmin dump_file,"<full_path_dump_file_name>"

go

sysadmin dum_queue, q_number, q_type, -1,0,-2

go

This commands dumps the queue from the begin to the end.

$cat XRDB.dmp | more

QUEUE DUMP FOR 103:0

BLOCK BEGIN q_number=103  q_type=0 blk=0:3 cnt=73

    Begin Transaction Origin User=sa Tran Name=_ins

      ENTRY ver=1100 len=216 orig=102 lorig=0 oqid=0000000000001e8c00001491001c00001491001a0000a4020120137d0000000000000001 lqid=0:3:0 st=4 tr= '0000000000001e8c001a' OnASE160XPDB '000000' f'00000000' comlen=88 begin transaction

      ENTRY ver=1100 len=260 orig=102 lorig=0 oqid=0000000000001e8c00001491001c00001491001a0000a4020120137d0000000000000002 lqid=0:3:1 st=-1071640576 tr= '0000000000001e8c001a' OnASE160XPDB '000000' f '00000000' comlen=132 insert into dbo.abc (c1, c2) values (1, 'one')

      ENTRY ver=1100 len=192 orig=102 lorig=0 oqid=0000000000001e8c00001491001c00001491001a0000a4020120137d0000000000000003 lqid=0:3:2 st=1 tr= '0000000000001e8c001a' OnASE160XPDB '000000' f'00000000' comlen=63 commit transaction

2. grep your table name in the dump file to list all the commands related to the table

$ cat XRDB.dmp |grep dbo.abc

      ENTRY ver=1100 len=260 orig=102 lorig=0 oqid=0000000000001e8c00001491001c00001491001a0000a4020120137d0000000000000002 lqid=0:3:1 st=-1071640576 tr= '0000000000001e8c001a' OnASE160XPDB '000000' f '00000000' comlen=132 insert into dbo.abc (c1, c2) values (1, 'one')

      ENTRY ver=1100 len=260 orig=102 lorig=0 oqid=0000000000001e9e0000149200020000149200000000a4020120137d0000000000000002 lqid=0:3:4 st=-1071640576 tr= '0000000000001e9e0000' OnASE160XPDB '000000' f '00000000' comlen=132 insert into dbo.abc (c1, c2) values (1, 'one')

      ENTRY ver=1100 len=260 orig=102 lorig=0 oqid=0000000000001e9e0000149200050000149200030000a4020120137d0000000000000002 lqid=0:3:7 st=-1071640576 tr= '0000000000001e9e0003' OnASE160XPDB '000000' f '00000000' comlen=132 insert into dbo.abc (c1, c2) values (1, 'one')

      ENTRY ver=1100 len=260 orig=102 lorig=0 oqid=0000000000001e9e0000149200080000149200060000a4020120137d0000000000000002 lqid=0:3:10 st=-1071640576 tr= '0000000000001e9e0006' OnASE160XPDB '000000' f '00000000' comlen=132 insert into dbo.abc (c1, c2) values (1, 'one')

Each command has its unique lqid which can be used  to locate it in the queue. lqid=seg:blk:row ( for example, lqid=0:3:4) are the parameters you need to run sysadmin sqm_zap_command

3. Zap the commands in replication server

sysadmin hibernate_on

go

sysadmin sqm_zap_command, q_number, q_type, seg, blk, row

go

sysadmin hibernate_off

go

For exampe, to delete lqid=lqid=0:3:4, the command is

sysadmin sqm_zap_cmmand, 103,0, 0,3,4

go

Or you can try this script to generate zap commands from the queue dump file and  submit sysadmin_zap.rcl in hibernation mode. You need review the generated commands before submiting it.

#!/bin/sh

#Usage : gen_zap_commands dump_file_name object_name queue_number queue_type

#Generate sqm_zap_command commands into a file named sysadmin_zap.rcl

grep  $2 $1 > TextTable_list.txt

awk '{print $7}' TextTable_list.txt | sed -e "s/lqid=/sysadmin sqm_zap_command, $3,$4 ,/;s/:/,/g;a\go" > sysadmin_zap.rcl

rm TextTable_list.txt

Binh