About fast BCP in Sybase

----------------------------------------------------------------------------

---- This is andkylee's personal originality. Please repost it with respect to the author's Labor achievements;

---- The original source must be specified for reprinting.
:
Http://blog.csdn.net/andkylee

---- Keyword: Sybase BCP fast log index fast BCP slow BCP full backup log backup

----------------------------------------------------------------------------

 

When copying data in Sybase, BCP (bulk copy) is often solved ).

BCP is divided into two types: Fast BCP and slow BCP. The two methods are for BCP in. For BCP out, there is no difference between fast and slow reading. It is to copy large data blocks from Sybase's extent.

 

When BCP data is in to a sybase server today, the following error is reported:

C:/Documents and Settings/Administrator> BCP test. DBO. tablename in D:/12 <br/> 34-C-USA-saix <br/> password: <br/> starting copy... <br/> Server Message: syb_aix53-MSG 4806, level 16, state 1: <br/> you cannot run the non-logged version of bulk copy in this database. please CHEC <br/> K with the DBO. <br/> Server Message: syb_aix53-MSG 3621, level 10, state 0: <br/> command has been aborted. <br/> ctlib message:-L1/O3/S0/N14/0/0: <br/> blk_init (): BLK layer: CT library error: failed when ct_lib routine ct_results () <br/> called. <br/> blk_init failed. <br/> BCP copy in failed

 

Check the error code 4806 on the error message & troubleshooting guide.

Explanation:

This error occurs when the select/into bulkcopy option is set to "false" and you
Use "fast" bulk copy into a table that has no indexes.
Note
In newly created databases, the Select into/bulkcopy option is set (
Default) to the same as that in model.

 

There are two solutions:

First:

Enable the "select/into bulkcopy" option for the target database and use the fast BCP mode. In this way, logs are not counted (in fact, few logs are recorded). That is to say, this does not guarantee database log consistency. It is best to make a full backup (dump database) for the target database ), before you can back up logs.

 

Note: To solve the problem that backup logs cannot be executed due to non-log operations, the only way is to perform full backup for the database. However, especially on the database opened by the Select into/bulk option, how do you know that this database has performed log-free operations? There is no good solution for versions earlier than 15.0. You can only prompt that logs cannot be backed up when the dump Tran command is issued. This is very inconvenient ~ <Br/> In analyticdb 15.0, the call is coming soon. Sybase sent us the gospel. Added the function tran_dumptable_status () to return a true/false indication of whether dump transaction is allowed. <Br/> If tran_dumpable_status returns 0, you can run the dump transaction command on the database. <br/>. If any other value is returned, the command cannot be executed. Non-zero values: <br/> 1-the database with the specified name does not exist. <Br/> 2-logs are not stored on a separate device. <Br/> 4-The log homepage is located in the data-only disk segment area. <Br/> 8-The trunc log on chkpt option is set for the database. <Br/> 16-an unrecorded write operation has occurred on the database. <Br/> 32-only troughs that have been interrupted by dump Tran are truncated. <br/>. <Br/> 64-the database has been recently created or upgraded. Transaction logs are not dumped <br/> before the dump database is executed. <Br/> now you can add tran_dumptable_status () to the backup script. If 0 is returned, the backup log task can be executed normally. If the return value is not 0, especially 16 or 32, the database is fully standby before the dump Tran operation.

 

 

Second, add an index to the table to be copied so that BCP can use the slow mode. In this way, the target database records logs normally. Just like an insert statement. Therefore, logs cannot be backed up after they are complete. However, be sure to prevent logs in the target database from being fully written due to a large number of write operations. You can execute dump transaction with truncate_only at the appropriate time when BCP in is in, or batch BCP import (you need to add the-B option ).