What is bcp in sybase

Number of rows to be batched together before committing to the server for bcp IN operations. Defaults to If there is a risk that retries could be requiered due to failed batches e. The default format for DATE fields in the input file. The parameter should be a symbolic value representing the format. BCP detects datetime targets by looking up the target table structure in the Sybase system tables.

The ordering of the fields in the input file does not correspond to the order of columns in the table, or there are columns that you wish to skip. In this example, field 1 of the input file goes in column 2 of the table, field 3 goes in column 1, field 2 goes in the column named foobar , and field 12 goes in column 4. Fields , and anything beyond 12 is skipped. As you can see you can use the column name instead of its position.

The default is to not do any reordering. The callback subroutine is called for each row after any reordering , and allows the user to do global processing on the row, or vetoing it's processing. It is supported for both bcp in and bcp out.

The path name can be from 1 to characters in length. Creation of the format file is optional. The default file name is bcp. The bcp program can refer to a format file when you are copying data so that you do not have to duplicate your previous format responses interactively.

Use the -f parameter only if you previously created a format file that you want to use now for a copy in or copy out. If you do not specify this parameter, bcp interactively queries you for format information.

Error messages from bcp appear on your terminal. Logs the rejected rows into a dedicated discard file. The discard file has the same format as the host file and is created by appending the input file name to the discard file prefix supplied. You can correct the rows in this file and use the file to reload the corrected rows.

Sybase recommends that you use the -d discardfileprefix option in conjunction with -e errorfile to help identify and diagnose the problem rows logged in the discard file. Avoid using the -F option when performing heavy-duty, multi-process copying, as it causes bcp to generally spend more effort to run, and does not provide you with a faster process.

Instead, use -F for single-process, ad-hoc copying. By default, bcp in copies n rows in one batch, where n is equal to the batch size. Batching applies only when you are bulk copying in; it has no effect on bulk copying out. The smallest number bcp accepts for batchsize is 1. The largest number bcp accepts for batchsize is L. Setting the batch size to 1 causes Adaptive Server to allocate one data page to one row copied in. This option only applies to fast bcp , and is only useful in locating corrupt rows of data.

Use -b1 with care—doing so causes a new page to be allocated for each row, and is a poor use of space. If you do not include this parameter, bcp uses a default value of Specifying the -n parameter means bcp will not prompt for each field. Files in native data format are not human-readable. Using bcp in native format can create flat files that cannot be reloaded into Adaptive Server and it may be impossible to recover the data.

If you cannot rerun bcp in character format for example, a table was truncated or dropped, hardware damage occurred, a database was dropped, and so on the data is unrecoverable. The discard file has the same format as the host file and is created by appending the input file name to the discard file prefix supplied.

You can correct the rows in this file and use the file to reload the corrected rows. Sybase recommends that you use -d discardfileprefix in conjunction with -e errorfile to help identify and diagnose the problem rows logged in the discard file. Error messages from bcp appear on your terminal. Sybase recommends that you use -e errorfile in conjunction with -d discardfileprefix to help identify and diagnose the problem rows logged in the discard file.

This is effective only when copying data into a table. If you specify the -E flag when copying data into a table, bcp reads the value from the data file and sends it to the server, which inserts the value into the table. The -E parameter has no effect when you are bulk copying data out. Adaptive Server copies the ID column to the data file, unless you use the -N parameter.

Creation of the format file is optional. The default file name is bcp. The bcp program can refer to a format file when you are copying data so that you do not have to duplicate your previous format responses interactively. Use the -f parameter only if you previously created a format file that you want to use now for a copy in or copy out.

If you do not specify this parameter, bcp interactively queries you for format information. If you use multiple files, this option applies to each file. Do not use -F when performing heavy-duty, multi-process copying, as it causes bcp to generally spend more effort to run, and does not provide you with a faster process.

Instead, use -F for single-process, ad-hoc copying. If you do not specify -I , bcp looks for an interfaces file sql. No conversion takes place. Use this if the client and server use the same character set. Omitting -J sets the character set to a default for the platform, which may not necessarily be the character set that the client is using. For more information about character sets and associated flags, see the Adaptive Server Enterprise System Administration Guide.

Used only with DCE security. Specifies a DCE keytab file that contains the security key for the user name specified with -U option. Create keytab with the DCE dcecp utility. See your DCE documentation. If you do not supply the -K option, the bcp user must be logged in to DCE with the same user name as specified with the -U option.

If you do not include this option, bcp uses a default value of Value values for LabelName are:. This is the upper bound to which you as a multilevel user can set your curread during the session. This is the upper bound to which you as a multilevel user can set your curwrite during a session. Sybase database is loaded on a unix box. Is BCP the way to handle this or is there any other way? If BCP is the way, can you please suggest where I can find examples or documentation related to this?

Go to www. Create a batch file that bulk-copies bcp out your data from to a delimited text file. Use TaskScheduler to automate execution on a regular basis. BCP will help you only half of the job extractring and creating the ascii file but for the automated part probably you should go for a shell scrip t scheduled on crontab.

I need to c reate an automated process that extracts data from Sybase table and creat es a delimited text file on a daily basis. If BC P is the way, can you please suggest where I can find examples or documen tation related to this? Please suggest. I also tried using iSQL and actually writing a query that selects all the columns and concatenates all of them into a single column separated by a pipe and then outputting it to a textfile.