----------------------------------------------------------------------------
Sender: owner-inews-technews-summary@maunaloa.Sybase.COM
Precedence: bulk
Reply-To: hvoltmer@sybase.com

                               SYBASE TECHNICAL NEWS

            Volume 7, Number 1                      January 1998

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

Introduction

This issue of Sybase Technical News contains new information about your
Sybase software. This newsletter is intended for Sybase customers. All
issues of Sybase Technical News are included on the Technical Information
Library web pages.

REMINDER: Sybase Ends Support for pre-11 SQL Server

Sybase support for Sybase SQL Server 10.x and 4.x ends in the Americas and
Asia/Pacific as of 1 January 1998. Support ends later in 1998 for Sybase
customers in Europe and Japan. For more information on end of support
status, and help with migrating to Sybase SQL Server 11.0.x or Adaptive
Server Enterprise 11.5, see our migration page or call Sybase Customer
Support.

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

In This Issue

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

Tip of the Month

TechNotes:

   * Configuration Block Corruption on Master Device
     http://techinfo.sybase.com/css/techinfo.nsf/DocId/ID=20148
   * Calling SQL Anywhere 5.0 Procedures and Functions from PowerBuilder
     http://techinfo.sybase.com/css/techinfo.nsf/DocId/ID=47756
   * Frequently Asked Questions about bcp (revision)
     http://techinfo.sybase.com/css/techinfo.nsf/DocId/ID=102

Other Useful Information

Subscription

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

Tip of the Month: Changing Permissions on Sybase Shared Libraries Increases
Performance on HP-UX

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

Although Sybase installs shared libraries with permission levels other than

-r-xr-xr-x

(555), to allow for future updating, users should change the permissions on
Hewlett-Packard PA-RISC machines to 555 to increase performance.

Why Performance is Affected

A shared library is implemented as a memory mapped file. If the file has 555
permissions, then the kernel can assign the file's pages a protection ID of
0. This is a public ID, which is usable for pages to which all processes
have equal access. If the file has permissions such as 755, then the kernel
must assign a unique protection ID to the file's pages. (Recent HP-UX
releases have assigned public protection IDs to a larger set of
permissions.)

HP-UX may use unique IDs for per-process checking to determine whether a
process has write permission for a memory location. A per-process check, of
course, slows performance.

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

TechNotes

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

Configuration Block Corruption on Master Device

Problem Description

Sybase SQL Server or Sybase MPP may occasionally experience corruption of
the configuration block on the master device due to several possible
conditions. In most cases, the cause is not a Sybase error. However, rare
cases have been traced to a Sybase bug.

This document helps you determine whether you have configuration block
corruption and whether a Sybase bug caused the corruption. It provides
information about rebuilding the configuration block to get your server back
on line and tells you what Sybase can do about the problem.

Diagnosing Configuration Block Corruption

The corruption causes SQL Server or MPP to stop and prevents them from
booting with this error message:

kdrdconfig: no config area onmaster disk

 Note: Although server configuration files (.cfg files) were introduced in
       11.0, some configuration functions remain in the configuration
       block. This area must be available to the 11.x server in order for
       it to boot.

Diagnosing the exact cause of configuration block corruption may be
difficult. Several types of user error as well as hardware failure can be
responsible for the problem. Less commonly, a Sybase bug may be the cause.
The following table lists some causes of configuration block corruption and
may help you diagnose the problem.

                        Table 1: Causes of Corruption

   Change
  Request    Version(s)                                              Fix
   Number     Affected          Description of the Problem         Version
  (Bug ID)

 Not a                  Hardware failure, which must be diagnosed
 Sybase bug n/a         by your system administrator.             n/a

 Not a                  Partition configuration error:
 Sybase bug n/a         Overlapping raw partitions, or file       n/a
                        system placed on device raw partition.

 Not a                  Disk init error: Logical volume manager
 Sybase bug n/a         requires vstart argument with the disk    n/a
                        init command and this was omitted.

                        Under some racing conditions between
                        updaters and deleters, a buffer may be
                        erroneously written to virtual address 0,
                        the first page of the config block. If
 122520     pre-11.5    you have a Sybase page (log, text,        11.0.3.2
                        statistic type) in the first block of
                        your master device, you probably are
                        experiencing this bug. Users unfamilar
                        with Sybase pages should call Technical
                        Support.

 Note: If you are not sure what caused the corruption, call Sybase
       Technical Support. See Help from Sybase Technical Support.

Workarounds

In order to get SQL Server back on line, you need to rebuild the master
device. The procedures to do this differ between pre-11.0, and 11.0 and
later releases.

System 10

Rebuild Configuration Block (Windows NT)

  1. To rebuild the configuration block with default settings, enter the
     following command:

     BLDMASTR.EXE -dmaster_device
      -r

  2. Start the server, then re-enter any configuration changes you have made
     using sp_configure .

Rebuild Configuration Block (UNIX)

  1. To rebuild the configuration block with default settings, enter the
     following command:

     buildmaster -dmaster_device
      -r

  2. Start the server, then re-enter any configuration changes you have made
     using sp_configure .

Rebuild Configuration Block (VMS)

  1. To rebuild the configuration block with default settings, enter the
     following command:

     buildmaster/disk=master_device
     /reconfigure

  2. Start the server, then re-enter any configuration changes you have made
     using sp_configure .

System 11.0.x

Rebuild Configuration Block (Windows NT)

  1. TheBLDMASTR.EXE -r command was disabled in release 11.0. Therefore, the
     task of rebuilding the configuration device is more complicated. The
     steps are documented in detail in the Sybase SQL Server Troubleshooting
     and Error Messages Guide and the Sybase SQL Server System
     Administration Guide . See these manuals in SyBooks at
     http://sybooks.sybase.com or run the SyBooks CD-ROM.

Rebuild Configuration Block (UNIX)

  1. The buildmaster -r command was disabled in release 11.0. Therefore, the
     task of rebuilding the configuration device is more complicated. The
     steps are documented in detail in the Sybase SQL Server Troubleshooting
     and Error Messages Guide and the Sybase SQL Server System
     Administration Guide . See these manuals in SyBooks at
     http://sybooks.sybase.com or run the SyBooks CD-ROM.

Rebuild Configuration Block (VMS)

  1. To rebuild the configuration block with default settings, enter the
     following command:

     buildmaster/disk=master_device
     /reconfigure

  2. Start the server, then re-enter any configuration changes you have made
     using sp_configure .

Help from Sybase Technical Support

If you have a problem with configuration block corruption and you have
eliminated non-Sybase causes to your satisfaction, contact Sybase Technical
Support. Support engineers may be able to provide more recent information
and suggest additional measures that you can take to restore your master
device and protect against future failures.

Preventative Measures

Bug #122520 can be avoided by:

   * Upgrading to Adaptive Server Enterprise 11.5.

   * Turning off the default "fix" option of dbcc tablealloc and dbcc
     indexalloc .

   * Running dbcc tablealloc or dbcc indexalloc after running update
     statistics . Do not run either of these dbcc commands concurrently with
     update statistics .

Backing Up the Master Device

Restoring the master device is easier when you have good backups of the
master database and other databases on the master device. In 11.0.x,
configuration information can be stored in .cfg files which allow you to
start the server with a specific session configuration. See the Sybase SQL
Server System Administration Guide for information on configuring and
backing up SQL Server.

Action from Sybase

This problem is fixed in Sybase SQL Server 11.0.3.2, scheduled for release
on most platforms by 1-January-1998. SQL Server 11.0.3.2 for OpenVMS is
scheduled for release in mid-January, 1998. This problem does not affect
Adaptive Server Enterprise.

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

Calling SQL Anywhere 5.0 Procedures and Functions from PowerBuilder

Calling SQL Anywhere 5.0 Procedures and Functions from PowerBuilder

Summary:

This document explains several methods of invoking stored procedures and
functions residing on a SQL Anywhere 5.0 database from within PowerBuilder.
This document covers building DataWindows based on the result sets of stored
procedures, as well as invoking stored procedures and functions from a
PowerBuilder script.

NOTE: Code examples in this document are in upper-case for clarity only.
Case is not important when you create your own procedures, functions and
PowerBuilder scripts.

Requirements

Functions and procedures can ONLY be called when using the following SQL
Anywhere database engines:

DBENG50.EXE (32 bit) or DBENG50W.EXE (16 bit) which is the standalone engine
you use within the PowerBuilder development environment.

DBCLIENT.EXE (32 bit) or DBCLIENW.EXE (16 bit) which you distribute with
your application (for complete information on purchasing the client and
server versions of the database engine as well as distribution licenses,
please contact PowerSoft Sales).

The Runtime database engine (RTDSK50.EXE) does not support the execution of
procedures and functions.

Creating the procedures and functions

The following SQL scripts create the procedures used in the examples in this
document. You can enter these scripts in the execute window of ISQL(one at a
time) while logged on to your database, then execute them to create the
procedures.

Script A

This function accepts an number as a Fahrenheit temperature value and
converts it to the Celsius value:

create function f2c (v_inval decimal(15,5))
returns decimal(15,5)
begin
declare v_outval decimal(15,5);
set v_outval = (v_inval - 32) / 1.8;
return (v_outval);
end
_______________________________________________

Script B

This procedure retrieves employee names from one table and the name of the
department they work in from a different table. It assigns the retrieved
columns to variables defined in the RESULT clause. The variables in this
clause should follow the order of the column names in the select list. Note,
that the variable names will not be used as the column names in your
DataWindow. You determine the column names displayed in the DataWindow by
editing the column name labels.

create procedure dept_members(in department_id integer)
result(
"Employee ID" integer,
"Employee Name" varchar(62),
Department varchar(25))
begin
select emp_id,emp_fname+' '+emp_lname,dept_name
from employee,department
where employee.dept_id=department.dept_id
and department.dept_id=department_id
end
_______________________________________________

Script C

This procedure selects calculated values from a database table, using an IN
variable for the WHERE clause of a SELECT statement, and an INTO clause
followed by the OUT parameters:

create procedure sp_mmasal_dept_1
(in department_id int,
out max_sal decimal(20,3),
out min_sal decimal(20,3),
out avg_sal decimal(20,3))
begin
select max(salary), min(salary), avg(salary)
into max_sal, min_sal, avg_sal
from employee
where employee.dept_id = department_id;
end
_______________________________________________

Script D

This procedure returns the same result as the previous one, but uses a
RESULT clause instead of OUT variables.

create procedure sp_mmasal_dept_2 (in department_id int)
result (
"Employee ID" int,
"Maximum Salary" decimal(20,3),
"Minimum Salary" decimal(20,3),
"Average Salary" decimal(20,3))
begin
select emp_id, max(salary), min(salary), avg(salary)
from employee
where employee.dept_id = department_id;
end
_______________________________________________

Script E

This procedure accepts two parameters, and inserts their values in a new row
in a table on the database. Before you create this procedure, make sure you
create a table with at least one integer column and one string column.

create procedure sp_testinsert(in var1 integer,in var2 char(10))
begin
insert into testinsert values(var1,var2)
end

Functions

There is only one way to invoke a function stored in a SQL Anywhere
database, which is to invoke it in a sql statement. This can be the sql
statement that a DataWindow is based on.

Use the function F2C created with script A above for this example.

To invoke this function in a PowerBuilder DataWindow:

1. Create a DataWindow as you normally would, using SQL Select as the Data
Source type. Use a table that has a column of numbers that you want to apply
this function to (the conversion won't be written back to your database).

2. Click on the Compute tab, and enter the function name followed by "("
(without quotes).

3. Right-click on the first line and select Columns. Select the column to
which you want to apply the function. Then close the parentheses. The final
syntax in the Compute tab should look like this:

F2C(TableName.ColumnName)

Optionally, you can start with a column name that will be used as the column
title in your DataWindow. In that case the final syntax would look like
this:

"Temperature in Centigrades" = F2C(TableName.ColumnName)

Preview the DataWindow. You should get a column containing the converted
values, which are the result of the function being applied to each value in
the column.

Procedures

Stored procedures can be invoked either as Data Sources for DataWindows , as
Remote Procedure Calls or within PowerBuilder scripts. There are several
methods to create a stored procedure on a SQL Anywhere database. The method
you use will also determine which method can be used to invoke the stored
procedure in PowerBuilder.

For DataWindows

In order to use a stored procedure as the Data Source for a DataWindow, it
must contain a RESULT clause. Also, it should contain ONLY IN parameters.

Use the procedure DEPT_MEMBERS() created with script B for this example

To invoke this stored procedure:

1. Create your DataWindow as you normally would, using Stored Procedure as
the Data Source type.

2. Preview the DataWindow, then save it. When prompted, enter the values for
the IN parameter(s). PowerBuilder will generate one prompt for each IN
parameter.

3. IN parameters are treated by PowerBuilder as retrieval arguments. When
you execute a DataWindow.Retrieve() on this DataWindow in a PowerBuilder
script, you should enter variables of the correct data type, in the correct
order, to match the parameter list for your stored procedure.

Remote Procedure Calls

A stored procedure that you intend to invoke as an RPC should NOT have a
RESULT clause.
RPCs are suitable for calling procedures that have IN, OUT and IN OUT
parameters.

Use the procedure SP_MMASAL_DEPT_1() created with script C for this example.

To invoke the last procedure as a Remote Procedure Call:

1. Create a non-visual user object of transaction type, and make it the
default transaction object. For complete information on creating this
transaction object, see your PowerBuilder documentation (PowerBuilder 5.0,
"Application Techniques" book, Chapter 15).

2. Paste the name of the procedure in the Declare Local External Functions
window. For this example, the declaration for this procedure should look
like this:

subroutine sp_mmasal_dept_1(long department_id,ref decimal max_sal,ref
decimal min_sal,ref decimal avg_sal) RPCFUNC ALIAS FOR
"~"dba~".~"sp_mmasal_dept_1~""

3. Save the object.

4. In a PowerBuilder script, invoke the RPC:

decimal ld_max, ld_min, ld_avg
long li_deptid

// This line gets a value from a single line edit on the window
li_deptid = long(sle_4.text)

// This line invokes the RPC passing all parameters.
sqlca.sp_mmasal_dept_1(li_deptid, ld_max, ld_min, ld_avg)

// Display the results in single line edit boxes on the window.
sle_1.text = string(ld_max)
sle_2.text = string(ld_min)
sle_3.text = string(ld_avg)

The procedure SP_TESTINSERT() created in script E above, can also be invoked
as a Remote Procedure Call.

Embedded SQL in PowerBuilder scripts

Stored procedures that you intend to invoke from embedded SQL statements in
PowerBuilder scripts MUST have a RESULT clause if you want them to return
results to PowerBuilder.

Use the procedure sp_mmasal_dept_2() created with script D for this example:

This PowerBuilder script invokes this procedure in an embedded sql
statement:

integer li_deptid
decimal ld_max, ld_min, ld_avg

// This line gets a value from a single line edit into the variable.
li_deptid = integer(sle_4.text)

// Declare the procedure and execute it.
declare getsalaries procedure for sp_mmasal_dept_2(:li_deptid);
execute getsalaries;

// Fetch results from the procedure into pb variables, in the order they are
returned.
fetch getsalaries into :ld_max, :ld_min, :ld_avg;

// Close the procedure.
close getsalaries;

// display the results.
sle_1.text = string(ld_max)
sle_2.text = string(ld_min)
sle_3.text = string(ld_max)
_______________________________________________

Use the procedure Dept_members() created in script B for this example.

This PowerBuilder script invokes this procedure in an embedded sql
statement. The difference between this example and the previous one is that
the previous procedure produced a single result "row" and therefore needed a
single FETCH statement, while this one produces multiple rows, requiring a
loop to repeat the FETCH as many times as necessary.

int li_empid
string ls_empname, ls_deptname

// This line gets a value from a single line edit on the window into a
variable.
ii_deptid = integer(sle_1.text)

// Declare and execute the procedure.
declare myproc procedure for dept_members(:ii_deptid);
execute myproc;

// Start a loop to fetch one row at a time and process each row
do while sqlca.sqlcode <> 100
fetch myproc into :li_empid, :ls_empname, :ls_deptname;
...
(your data processing logic goes here)
...

loop

// Close procedure
close myproc;
_______________________________________________

Dynamic SQL Syntax

PowerBuilder's dynamic SQL syntax formats 1 and 2 can also be used to invoke
a SQL Anywhere stored procedure. However, formats 1 and 2 are strictly
limited to SQL statements and procedures that do not return results. You can
use these procedures to execute all forms of DDL and DML.

For complete information on the four dynamic syntax formats, see the
PowerBuilder documentation, "PowerScript Reference" Chapter 6, "SQL
Statements."

Use the procedure SP_TESTINSERT() created in script E above for the next two
examples.

Dynamic SQL Syntax format 1

string ls_statement, ls_var1, ls_var2

// This line loads values entered into single line edits on the window to
variables in the script.
ls_var1 = sle_1.text // The value here is a number.
ls_var2 = sle_2.text // The value here is a string.

ls_statement = "call sp_testinsert(" + ls_var1 + ",'" + ls_var2 + "')"
execute immediate :ls_statement;

The disadvantage with this method is that you have to be careful to
concatenate single quotes before and after a value that needs to go into a
string column on the database.
________________________________________________

Dynamic SQL Syntax format 2

string ls_statement, ls_var1, ls_var2

// This line loads values entered into single line edits on the window to
variables in the script.
ls_var1 = sle_1.text // The value here is a number
ls_var2 = sle_2.text // The value here is a string.

prepare sqlsa from 'call sp_testinsert(?,?)';

execute sqlsa using :ls_var1, :ls_var2;

This method uses a question mark as a placeholder for the parameter being
passed to the procedure. This restricts your flexibility because you cannot
build the string 'call sp_testinsert(?,?)' at runtime (attempting to use a
string variable after the FROM keyword in the example above will produce a
syntax error). The advantage is that you don't have to concatenate quotes
before and after string values.

Additional references:
Sybase SQL Anywhere User's Guide
PowerBuilder documentation: Application Techniques, User's Guide,
PowerScript Reference
----------------------------------------------------------------------------

Frequently Asked Questions about bcp (revision)

Contents

This document answers some frequently asked questions about the Sybase bulk
copy program, bcp :

   * What version of bcp can I use with my server?

   * What is the form of storage of bcp data?

   * What are the privileges required to bcp data?

   * What is fast and slow bcp ?

   * Can bcp copy out an entire database?

   * What database options must be set for bcp ?

   * How can I make my fast bcp process perform better under System 11?

   * Will the row in sysindexes for the text or image column(s) affect
     whether I can do fast bulk copy?

   * How does bcp delimit data?Are delimiters mandatory?

   * What is a format file and when should I use it?

   * How does bcp handle null data?

   * When I copy out text data, only the first 32K are copied. Why? How can
     I get all of the data copied?

   * Why was my bcp job logged when my table has no indexes?

   * If a table has triggers on it, are the triggers fired in slow bcp ? How
     about rules on a table?

   * Can I bcp data simultaneously into several table partitons?

   * Can I use two processes at once to bcp data into one table if the table
     is not partitioned?

   * How does bcp handle IDENTITY columns?

   * How do I use 11.5 parallel bcp for tables with IDENTITY columns?

   * How can I trap errors in bcp ? What kind of errors does bcp trap?

   * How do I record rows that the server rejected during a bcp operation?

   * My table has 10 columns but my datafile has 12. How can I specify that
     only certain columns be copied in?

   * All the columns in a datafile from an external source have quotes
     around them. How can I copy in the data without the quotes, if I don't
     want to edit the file?

   * How do I fix an "Unexpected EOF" when copying data from a host file to
     a Sybase table as a bcp operation?

   * My server is on Solaris, but I am trying to bcp out the data from my HP
     box. Should I be concerned about conversion issues?

   * Why doesn't bcp work correctly on VMS?

What version of bcp can I use with my server?

We recommend that you use the version of bcp that is shipped to you with
your server:

   Server Version            Shipped bcp Version

 SQL Server 11.0.x   10.0.2 or higher with the latest EBF

 Adaptive Server 11.511.1.1

You can use any 10.0.x or 11.x bcp version with any 10.0.x or 11.x server
version.

What is the form of storage of bcp data?

bcp can store data in either ASCII or native format. ASCII format is the
same as storing in character mode, which is human readable. Native format
stores data in operating system format or binary mode, which is not human
readable.

What are the advantages of one format over the other?

The advantages and disadvantages of each are:

ASCII Format (Recommended)

Advantages:

   * Portable across all platforms

   * Easy to read and, thus, easy to debug

   * The only method recommended for importing external data sources, such
     as COBOL or Microsoft Access

Disadvantages:

   * Requires conversion from and to server datatypes and character format
     for every data value

   * Takes slightly more space than data stored in native format

Native Format

Advantages:

   * Takes less disk space than character format

   * Does not require conversion to character format, which means better
     performance

Disadvantages:

   * Not compatible across platforms

   * Not compatible across external data sources

   * Even across servers, types differ from release to release which may
     result in incompabilities

   * Not human readable, thus very difficult to debug

What are the privileges required to bcp data?

To bcp data, you need:

   * A valid SQL Server or Adaptive Server account

   * Appropriate permissions on the database tables and operating system
     files:
        o To copy in data, you need insert permissions on the table.

        o To copy out data, you need select permissions on the table being
          copied, as well as on the system tables sysobjects, syscolumns,
          and sysindexes.

What is fast and slow bcp ?

Any bcp...in operation performed on a table with no triggers or indexes is
fast bcp because inserts are not logged.

If the table contains indexes or triggers, bcp logs all inserts in the
transaction log, which is called slow bcp because the logging slows down the
operation. Remember, logging inserts can cause the transaction log to fill
up very quickly.

Can bcp copy out an entire database?

No, not directly. You can, however, simply use a shell script to copy out an
entire database.

What database options must be set for bcp ?

To enable the use of fast bcp..in for tables without indexes or triggers,
the system or database administrator for the database turns on the select
into/bulkcopy/pllsort option (in pre-11.5, called select into/bulkcopy ).

If select into/bulkcopy/pllsort is turned off (false is the default) when
you try to bulk copy data in, the following error occurs:

You cannot run the non-logged version of bulk copyin this database. Please check with the DBO.

To set the database option to true :

  1. Use the isql command:

     sp_dboption dbname
     , "select into/bulkcopy/pllsort", true

  2. Issue a checkpoint in the database.

 Note: select into/bulkcopy/pllsort does not need to be turned on for
       bcp..out or slow bcp .

How can I make my fast bcp process perform better under System 11?

There are several ways to increase performance. See the Performance and
Tuning Guide and the Utility Programs manual for guidelines for performing
these tasks:

   * Increase extent allocation. Currently extents (8 pages) are allocated
     one at a time. You can preallocate 2 to 31 extents at a time. Any
     unused extents that were preallocated within each bcp batch are
     de-allocated. For maximum performance, size your bcp batch and set the
     number of pre-allocated extents parameter (previously known as
     cpreallocext ) to eliminate any de-allocations. For System 11, use:

     sp_configure "number of pre-allocatedextents", nn

     For System 10, use:

     buildmaster -ycpreallocext = nn

   * Partition the table.Also see Can I bcp data simultaneously into several
     table partitions?

   * Configure the OAM page caching to reduce physical reads of OAM (Object
     Allocation Map) pages:
        o For 11.5:

          sp_configure "number of oam trips"

        o For pre-11.5:

          sp_configure "number of coamtrips"

   * Set memory low and buffer wash high (80%), so that the I/O executes
     continually to take advantage of idle cycles. Normal bcp flushes pages
     during checkpoint or end of batch.

     For more information on setting memory (total memory ) see the
     Performance and Tuning Guide , and setting buffer wash (housekeeper
     free write percent ) see the System Administration Guide .

   * Configure a buffer pool to a large I/O size such as 16K:

     sp_poolconfig default, "16K"

   * Increase the network packet size to the network I/O size with the -A
     flag:

     bcp -A 16384

 Note: Depending upon the size of the bcp job and the tuning required, you
       may want to create a configuration file that maximizes bcp
       performance. You will need to restart the server, specifying the new
       configuration file, to execute the file. See the System
       Administration Guide for details on creating configuration files.

Will the row in sysindexes for the text or image column(s) affect whether I
can do fast bulk copy?

No, text or image columns will not prevent fast bulk copy into a table that
has no indexes. bcp checks the sysstat column of sysindexes. Bits in sysstat
indicate whether the index is clustered (O_CLUST 0x10) or nonclustered
(O_NONCLUST 0x020). Neither of these bits are set in sysindexes rows for
text or image columns.

How does bcp delimit data? Are delimiters mandatory?

By default, bcp uses tabs to delimit columns and new lines as row
terminators. To change them, use the -t (field terminator) and -r (row
terminator) options. The terminator can be as long as 30 characters.

You must use double quotes. For example:

bcp tempdb..testout data -c -t "," -r "###" -Uuser
-Ppassword

In this example, every column copied out of table test will be delimited by
commas (,) and every row by pound signs (###). You would then use the same
options when copying in the data.

 Note: Be sure to use double quotes if the terminator is a special shell
       character.

The use of delimiters is highly recommended, because character and text
datatypes can have the default delimiters (tab and new line) embedded in
them. bcp cannot distinguish between the actual delimiters and those
embedded in the data. Always use a delimiter pattern that is unlikely to be
in the data.

What is a format file and when should I use it?

A format file describes the layout of data in the datafile, such as:

   * bcp version

   * Number of columns in the host file to be copied

   * Type of data that it contains for each column (sybchar for all types in
     an ASCII file)

   * Length of the data

   * Delimiters

   * Server column into which the value is to be copied

Use a format file when the data is in a format different from the defaults,
which are tab delimited columns and row delimited data. For example, you
would use a format file when:

   * Datafile is in fixed length format. Datafiles from mainframe sources
     are often defined this way.

   * Sequence of data in the datafile does not match that in the
     corresponding server table.

   * Number of columns in the datafile does not match that in the
     corresponding server table.

How does bcp handle null data?

For bcp to consider a value as null, there must be nothing between the
column delimiters. A space is not considered as a null. Depending on the
datatype of the column in the server, the value is translated accordingly.

Consider the following datafile:

1,hello,01/01/96
, ,
,,

If we copy this data into a table defined as:

create tabletest(col1 int null, col2 char(10)null, col3 datetime null)

with the command:

bcp test in data -c -t"," -Uuser
 -Ppassword

then a select from the table would contain the following values:

 col1       col2               col3

 1   hello              Jan 1 1996 12:00AM

 NULLJan 1 1900 12:00AM


 NULLNULL               NULL

As the example shows:

   * A space in a fixed length field, such as an integer, is considered as a
     NULL.

   * A space in a char/varchar field is considered a space.

   * A space in a datetime field is inserted as the default date, Jan 1
     1900.

When I copy out text data, only the first 32K are copied. Why? How can I get
all of the data copied?

By default, the server copies out only 32K of text or image data for
bcp..out . To retrieve all of the text or image data using bcp , use the -T
option, specifying the size of text data to be retrieved. For example:

  1. Find out the maximum size of the data in the table. In isql , enter:

     select max(datalength(text_col)) from table

     Use the returned value as the size of the text data to be copied out.
     Alternatively, you can skip this step and specify the largest text size
     possible, 2147483647.

  2. Specify the size of the text data with the bcp command:

     bcp dbname.owner.table in datafile -c -Uuser
      -Ppassword
      -T100000

     In step 2, the size of the text data copied out is 100000 bytes.

Why was my bcp job logged when my table has no indexes?

Although a table has no indexes, adding data to the table with bcp can cause
the log to fill up because bcp logs page allocations. If a table has no
index and the select into/bulkcopy/pll option is set for that database,
there should be no logging of the new rows added. For details, see the
Utility Programs manual.

If a table contains triggers but no index, the server uses slow bcp with
full logging, but doesn't fire the triggers. You can either:

   * Drop all triggers on the table before running bcp and then re-create
     them when bcp is finished.

   * Use slow bcp to retain the indexes and triggers on the table.

If a table has triggers on it, are the triggers fired in slow bcp ? How
about rules on a table?

For performance reasons, bcp does not fire triggers on tables or check for
rules. For details, see the information on data integrity--defaults, rules
and triggers--in the Utility Programs manual.

Can I bcp data simultaneously into several table partitions?

A single bcp process cannot insert data into several table partitions as of
release 11. However, you can use more than one bcp process to insert
simultaneously into several partitions. The Utility Programs manual gives
guidelines for bulk copying data into partitioned tables.

Parallel Bulk Copy

As of release 11.0, you can use parallel bcp to copy data into a specific
partition. Splitting large bulk copy jobs into multiple client sessions that
run concurrently is faster than running one large job.

The number of parallel bcp sessions that you can start is limited by the
number of partitions in the table. For example, if you start five bcp jobs
on a table with four partitions, the first four jobs run in parallel; the
fifth job starts when one of the other four jobs finishes.

Also see TechNote 1271, FAQs about Table Partitioning , under "How Do I Take
Advantage of Table Partitioning with Parallel bcp in?".

As of release 11.5, you can balance partitions by specifying partition
numbers. This enables you to use bcp to greatly reduce partition skew; that
is, to balance the amount of data among partitions.

To benefit from release 11.5 parallel bulk copy, do not use tables with
clustered indexes. If you do, slow bcp is used; and the clustered index
determines data placement, not bcp .

 Note: Copying in very large tables, especially simultaneous copies into a
       partitioned table, can require a large number of locks. To avoid
       running out of locks, consider resetting the number of locks
       parameter and using the --b batchsize option to copy smaller
       batches.

Can I use two processes at once to bcp data into one table if the table is
not partitioned?

Yes, two processes can copy data to the same table at the same time, whether
or not the table is partitioned. If your table is not partitioned, there is
a greater likelihood of contention; for example:

   * If one bcp process escalates to a table lock, the other will be
     blocked.

   * If both bcp processes try to insert into the same place, one will block
     the other. When adding rows to a table with no clustered index, the new
     rows are added to the end of the table (last page) and one bcp process
     will block the other. It is possible for the two bcp processes to
     deadlock.

How does bcp handle IDENTITY columns?

For tables with IDENTITY columns, you can either let the server assign the
identity values for the rows being bulk copied in or you can obtain them
from the bcp host file.

By default, SQL Server or Adaptive Server assigns values for the bcp
IDENTITY column. This means that the datafile does not need to contain the
values. You can choose which values you want to use:

   * Use datafile values. If the datafile contains identity values and you
     want to use them instead of the ones the server assigns, use the -E
     flag as follows:

     bcp dbname.owner.table in datafile -c -E -Uuser
      -Ppassword

      Note: The server does not guarantee uniqueness with the -E flag. You
            must guarantee unique values.

   * Use server defaults. If the datafile contains identity values and you
     do not want to use them instead of the ones the server assigns, use the
     -N flag as follows:

     bcp dbname.owner.table in datafile -c -N -Uuser
      -Ppassword

     The server then assigns new identity values.

How do I use 11.5 parallel bcp for tables with IDENTITY columns?

When using multiple bcp...in processes to a table with an IDENTITY column,
prevent locking contention and ensure sort order by using the --E or --g
parameters. Be careful to ensure unique values.

For details on how to use the --E and --g parameters, see the Utility
Programs manual.

 Note: If you use native format and explicit identity values, copying out
       with bcp --n and in with bcp --n --E , you receive an error (Bug
       #125962). Use character format (-c ) instead.

How can I trap errors in bcp ? What kind of errors does bcp trap?

The -e option to bcp records errors that occur during a bulk copy operation.
bcp traps errors in syntax, conversion and format files.

Not all errors are recorded. Only errors that bcp itself recognizes are
written to the error file. Errors reported by the server, such as duplicate
rows or full log, are not written to the error file but are returned to the
screen.

How do I record rows that the server rejected during a bcp operation?

To trap rows rejected by the server for reasons such as duplicate rows or
character conversion errors, write a bulk copy program using Client-Library
to handle the exceptions. In the bulk copy program, do the following:

  1. Set the batch size to 1.

  2. Bind the data to local variables.

  3. Send the row to the server.

  4. If the return code is not successful, write the variables containing
     the data for the current row to an error file.

My table has 10 columns but my datafile has 12. How can I specify that only
certain columns be copied in?

You need a format file to specify what columns to copy in. Use the server
column order in the format file to specify the table column in which to copy
a value. If you do not want the value in the datafile copied in, specify the
server column as 0. This column in the datafile will be 'skipped'.

For example, if we define a table as:

create table test(idint, name char(25), state char(2))

and the datafile contains four columns:

100,New Age Books,Oakland,CA
101,Bennet& Brown,Washington,DC
102,Algodata Systems,Boston,MA

then the format file would contain:

 10.0


 4


 1   SYBCHAR 0 4   "," 1 id

 2   SYBCHAR 0 255 "," 2 name

 3   SYBCHAR 0 255 "," 0


 4   SYBCHAR 0 2   "\n"3 state

All the columns in a datafile from an external source have quotes around
them. How can I copy in the data without the quotes, if I don't want to edit
the file?

You can "trick" bcp into copying the data without quotes by what you specify
in the format file for prefix lengths and terminators.

For example, let's say the datafile contains the following:

"1","hello","01/01/96"
"2","world","01/01/97"

We can copy these values into a test table as:

create tabletest(col1 int null, col2 char(10) null, col3 datetime null)

The format file would contain:

 10.0


 3


 1   SYBCHAR 1 10 "\"," 1 col1

 2   SYBCHAR 1 10 "\"," 2 col2

 3   SYBCHAR 1 10 "\"\n"3 col3

Based on these specifications:

   * A prefix length of 1 causes bcp to ignore the beginning quote for every
     column because it looks like a prefix to bcp .

   * A terminator as the ending quote plus the column/row terminator itself
     means bcp does not consider it as part of the data.

How do I fix an "Unexpected EOF" when copying data from a host file to a
Sybase table as a bcp operation?

The error means that bcp has found a problem with the host datafile and
cannot proceed. There are a variety of reasons that this can happen. The
most common ones are:

Blank Lines in Host File

Blank line(s) in the host datafile. Any completely blank line, or one that
contains spaces, is interpreted as a null record. Remove all blank lines
and/or null records.

Data conversion from EBCDIC format

Converting data from EBCDIC format (typically IBM mainframes) to ASCII can
leave some unconverted characters. Utility programs on platforms such as
UNIX and VAX/VMS can perform most, if not all, of the translation properly.
However, if the utility encounters an EBCDIC character that it cannot
translate, the character is left untranslated. These are often "overstrike"
characters.

When bcp encounters the untranslated character, the result is the
"Unexpected EOF" message. The resolution to the problem depends on your
needs:

   * In one-time or ad hoc situations, simply edit the host datafile to
     remove and/or replace the character(s) in question. Inspect the data to
     find out where the copy failed; bcp does not tell you where it failed.
     If necessary, cross-reference an EBCDIC collating sequence chart to
     determine which characters caused the problems.

   * If you frequently load translated data, especially if you load
     translated data into your server as part of an automated process, you
     may need to create your own translation script, program, or command
     file. Use it to perform the EBCDIC-to-ASCII translation before you run
     the bulk copy itself.

The advantage of programming is two-fold:

   * Let's say that you have data that is not translatable using a
     "standard" translation table, and you have a "special" translation
     table to handle what the standard translation does not define. The
     program can create an exception file to deposit the uncopied records
     for further inspection and resolution.

   * The program can perform bcp with a finer degree of control using bulk
     copy function calls.

     The host datafile contains control or garbage characters, such as
     control-M ("hard return"). In particular, "noisy" data transmission can
     generate characters outside the range of a platform's collating
     sequence.

Terminator Character Embedded in Data Field

A character being used as a terminator character, such as a comma, for a
field or record is embedded as part of the field itself. Embedding often
happens when you import data from a PC program where data is exported in
"comma-delimited format". Sometimes, the "Unexpected EOF" error occurs. In
other cases, the server data table is not correct due to misalignment of the
data.

Carefully check your host datafile parameters and content to be sure that
the delimiters are not included in the data. For automated transfers, you
may need filters or programs when copying data from host file to Sybase
table as a bcp operation. See "How do I fix an "Unexpected EOF...?"

Why doesn't bcp work correctly on VMS?

Input files must be in STREAM_LF format. EDIT/FDL can be used to convert
files into this format.

Incorrect File "record format" on VMS

On VAX/VMS platforms, the "Unexpected EOF" error can occur because of an
incorrect file "record format". bcp requires a record format STREAM_LF. To
see the record format, type:

 DIR/FULL file_name

If the file was created by an editor, it is probably "variable length,
maximum xxx bytes". Files created by bcp when copying a database table to a
host file are in the correct format.

To correct a record format to STREAM_LF, perform the following steps:

  1. Create a file FILENAME.FDL containing VMS File Description information,
     parameters and controls:

     ANALYZE/RMS/FDL 

  2. Edit FILENAME.FDL. Under the RECORD section, change the FORMAT
     specification to STREAM_LF.

  3. Create a new version of the filename.dat in STREAM_LF record format:

      CONVERT/FDL=filename filename.dat filename.dat

     The ".fdl" extension is the default in "FDL=filename". If you want a
     different file name for the conversion instead of the next version,
     replace the second filename.dat with newfilename.dat .

My server is on Solaris, but I am trying to bcp out the data from my HP box.
Should I be concerned about conversion issues?

The default character set for HP machines is roman8, and that for Sun
machines is iso_1. These are operating system standards and are not specific
to Sybase, although Sybase follows them. If the character set of the client
is different from that of the server, as in this case, the server displays a
message to let you know that the character sets are different. If the server
can perform the conversion between the two character sets, it does.

Typically, you will not have problems with the conversions, unless
characters in the data are specific to one character set. In this case, you
can expect the following behaviour:

   * bcp..in results in Error 2402:

     Error converting client characters into server's character set. Some character(s)could not be converted.

     The server must guarantee that the data successfully converted during
     input to the server can be successfully converted back to the client's
     character set. The server cannot copy suspect data into the database.
     The bulk copy execution stops on this error.

   * bcp..out results in Error 2403:

     WARNING!  Some character(s) could not be converted into client's characterset. Unconverted bytes were changed to question marks ('?').

     After displaying this message, the server substitutes "?" for any
     character it could not convert to the client's character set.

If you get one of the above messages, specify a character set that is common
to both environments. The bcp -Jcharset option enables you to specify the
clients' character set; that is, the character set used on the client. The
value you specify for -J directs the server to convert to/from that
character set to/from the servers' character set.

Example

Copying Out

Let's say the server is on Solaris and you want to copy out the data from a
client on a HP box. You might first issue the command:

bcp table_name
 out datafile
 -c -Uuser
 -Ppassword

If the server stops the bcp job with Error 2403, you can perform one of the
following options:

   * Use the -J option without specifying a character set. No conversion
     will take place and no error message will be displayed. Some characters
     sent to the server may not be interpreted correctly, typically non
     7--bit characters.

   * Use the -J option and specify a character set, such as iso_1. The
     server converts the client character set to iso_1. Keep in mind that,
     in this case, if you copy the data into an HP server with a default
     roman8 character set, the datafile contains characters in iso_1 format.

Copying In

To copy in data when the platforms are different, you could make use of
additional character sets installed on the server. For example, specify:

bcp test in data -c -Uuser
 -Ppassword
-Jroman8

In this case, if the roman8 character set is installed, the server does the
appropriate conversions. Otherwise, you get errors:

Msg  2409,Level 11, State 2: Cannot find the requested character set in Syscharsets: name = 'roman8'.

Msg 2411, Level 0, State 1: Noconversions will be done.

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

Other Useful Information

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

   * SQL Server 11.x Bug Report

             http://techinfo.sybase.com/css/techinfo.nsf/DocID/ID=2434

   * Certification Reports

             http://techinfo.sybase.com/css/techinfo.nsf/Certification

   * Product Availability

             http://techinfo.sybase.com/css/techinfo.nsf/New+Products

   * End-of-Life/End-of-Support

             http://techinfo.sybase.com/css/techinfo.nsf/Topic

   * Error Messages

             http://techinfo.sybase.com/css/techinfo.nsf/Topic

   * Migrating to Sybase Adaptive Server Enterprise 11.5, a guide to help
     you migrate from 4.x, 10.x and 11.0.x, including templates and
     checklists.

             http://techinfo.sybase.com/css/techinfo.nsf/DocID/ID=34982-01-1150-01

   * What's New

             http://techinfo.sybase.com/css/techinfo.nsf/Whats+New

For frequent updates, visit the Sybase Technical Information Library on the
World Wide Web at http://techinfo.sybase.com.

Electronic Case Management and Electronic Software Delivery access remains
limited to Sybase support contacts who are registered with SupportPlus
Online Services. ECM and ESD requires SSL web browser support. ECM and ESD
users also may access the Technical Information Library from behind the
firewall in the usual manner, allowing them to retain the security benefits
of using SSL with their web browser.

Subscription Information

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

Send comments to technews@sybase.com. To receive this document by regular
email, send email to majordomo@sybase.com, and in the body of your message
put subscribe inews-technews-full. For a summary version by email, put
subscribe inews-technews-summary. No subject line is necessary.

Staff

Betsy Brazy, editor

Contributors

Lance Andersen, Mehdi Bouzouina, Cris Gutierrez, Robert Pickering, Jenny
Schaffer, Farima Steimer, Mike Stroyan, Carol Talbeck.

Disclaimer

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

No express or implied warranty is made by Sybase or its subsidiaries with
regard to any recommendations or information presented in Sybase Technical
News. Sybase and its subsidiaries hereby disclaim any and all such
warranties, including without limitation any implied warranty of
merchantability of fitness for a particular purpose. In no event will Sybase
or its subsidiaries be liable for damages of any kind resulting from use of
any recommendations or information provided herein, including without
limitation loss of profits, loss or inaccuracy of data, or indirect special
incidental or consequential damages. Each user assumes the entire risk of
acting on or utilizing any item herein including the entire cost of all
necessary remedies.

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

--------------------------------------------------
If you ever want to remove yourself from this mailing list, you can send
mail to "majordomo@sybase.com" with the following command in the body of
your email message:

unsubscribe inews-technews-full


If you ever want to add yourself from this mailing list, you can send
mail to "majordomo@sybase.com" with the following command in the body of
your email message:

subscribe inews-technews-full