sybase® adaptive server™ enterprise utility programs for...

Sybase® Adaptive Server™ EnterpriseUtility Programs for UNIX Platforms

Document ID: 30475-01-1150

September 1997

Copyright Information

Copyright © 1989–1997 by Sybase, Inc. All rights reserved.

Sybase, Inc., 6475 Christie Avenue, Emeryville, CA 94608.

Information in this document is subject to change without notice. The software described herein is furnishedunder a license agreement, and it may be used or copied only in accordance with the terms of that agreement.No part of this publication may be reproduced, transmitted, or translated in any form or by any means,electronic, mechanical, manual, optical, or otherwise, without the prior written permission of Sybase, Inc.

Use, duplication, or disclosure by the government is subject to the restrictions set forth in subparagraph (c)(1)(ii)of DFARS 52.227-7013 for the DOD and as set forth in FAR 52.227-19(a)-(d) for civilian agencies.

Sybase, the Sybase logo, APT-FORMS, Certified SYBASE Professional, Data Workbench, First Impression,InfoMaker, PowerBuilder, Powersoft, Replication Server, S-Designor, SQL Advantage, SQL Debug, SQLSMART, SQL Solutions, Transact-SQL, VisualWriter, and VQL are registered trademarks of Sybase, Inc.Adaptable Windowing Environment, Adaptive Component Architecture, Adaptive Server, Adaptive ServerMonitor, ADA Workbench, AnswerBase, Application Manager, AppModeler, APT-Build, APT-Edit, APT-Execute, APT-Library, APT-Translator, APT Workbench, Backup Server, BayCam, Bit-Wise, ClearConnect,Client-Library, Client Services, CodeBank, Column Design, Connection Manager, DataArchitect, DatabaseAnalyzer, DataExpress, Data Pipeline, DataWindow, DB-Library, dbQ, Developers Workbench, DirectConnect,Distribution Agent, Distribution Director, Dynamo, Embedded SQL, EMS, Enterprise Client/Server, EnterpriseConnect, Enterprise Manager, Enterprise SQL Server Manager, Enterprise Work Architecture, Enterprise WorkDesigner, Enterprise Work Modeler, EWA, Formula One, Gateway Manager, GeoPoint, ImpactNow,InformationConnect, InstaHelp, InternetBuilder, iScript, Jaguar CTS, jConnect for JDBC, KnowledgeBase,Logical Memory Manager, MainframeConnect, Maintenance Express, MAP, MDI Access Server, MDI DatabaseGateway, media.splash, MetaWorks, MethodSet, Net-Gateway, NetImpact, Net-Library, ObjectConnect,ObjectCycle, OmniConnect, OmniSQL Access Module, OmniSQL Toolkit, Open Client, Open ClientConnect,Open Client/Server, Open Client/Server Interfaces, Open Gateway, Open Server, Open ServerConnect, OpenSolutions, Optima++, PB-Gen, PC APT-Execute, PC DB-Net, PC Net Library, Power++, Power AMC,PowerBuilt, PowerBuilt with PowerBuilder, PowerDesigner, Power J, PowerScript, PowerSite, PowerSocket,Powersoft Portfolio, Power Through Knowledge, PowerWare Desktop, PowerWare Enterprise, ProcessAnalyst,Quickstart Datamart, Replication Agent, Replication Driver, Replication Server Manager, Report-Execute,Report Workbench, Resource Manager, RW-DisplayLib, RW-Library, SAFE, SDF, Secure SQL Server, Secure SQLToolset, Security Guardian, SKILS, smart.partners, smart.parts, smart.script, SQL Anywhere, SQL Central, SQLCode Checker, SQL Edit, SQL Edit/TPU, SQL Modeler, SQL Remote, SQL Server, SQL Server/CFT, SQLServer/DBM, SQL Server Manager, SQL Server SNMP SubAgent, SQL Station, SQL Toolset, SybaseClient/Server Interfaces, Sybase Development Framework, Sybase Gateways, Sybase IQ, Sybase MPP, SybaseSQL Desktop, Sybase SQL Lifecycle, Sybase SQL Workgroup, Sybase Synergy Program, Sybase Virtual ServerArchitecture, Sybase User Workbench, SybaseWare, SyBooks, System 10, System 11, the System XI logo,SystemTools, Tabular Data Stream, The Architecture for Change, The Enterprise Client/Server Company, TheModel for Client/Server Solutions, The Online Information Center, Translation Toolkit, Turning ImaginationInto Reality, Unibom, Unilib, Uninull, Unisep, Unistring, Viewer, Visual Components, VisualSpeller,WarehouseArchitect, WarehouseNow, Warehouse WORKS, Watcom, Watcom SQL, Watcom SQL Server,Web.SQL, WebSights, WebViewer, WorkGroup SQL Server, XA-Library, and XA-Server are trademarks ofSybase, Inc. 6/97

All other company and product names used herein may be trademarks or registered trademarks of theirrespective companies.

Utility Programs for UNIX Platforms v

Table of Contents

About This BookAudience . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ixHow to Use This Book . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ixAdaptive Server Enterprise Documents . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ixOther Sources of Information . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiConventions Used in This Manual . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xi

Font and Syntax Conventions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiiIf You Need Help . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiii

1. Utility Commands ReferenceHow the UNIX Shell Interprets Utility Program Commands. . . . . . . . . . . . . . . . . . . . . 1-1backupserver. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-4bcp . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-7buildmaster . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-23dataserver . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-26defncopy . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-29dscp . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-34dsedit . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-35isql . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-36langinstall . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-44showserver . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-47sqlloc. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-48sqllocres . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-50sqlupgrade . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-52sqlupgraderes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-53srvbuild . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-54srvbuildres . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-56startserver . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-58sybload . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-61sybsetup . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-63

2. Using bcp to Transfer Data to and from Adaptive ServerMethods of Moving Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-1

Using bcp to Import and Export Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-2

vi

Adaptive Server Enterprise Release 11.5.x

Requirements for Using bcp . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-2Permissions Needed for Copying Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-3

bcp Performance Issues . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-3Using Fast or Slow bcp . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-4Steps for Copying Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-6Bulk Copying Data into Partitioned Tables . . . . . . . . . . . . . . . . . . . . . . . . 2-6

Guidelines . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-6Copying Data into Partitions Randomly . . . . . . . . . . . . . . . . . . . . . . . 2-7

Using Parallel Bulk Copy to Copy Data Into a Specific Partition . . . . . . 2-9bcp in and Locks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-10Parallel Bulk Copy Methods. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-11Using Parallel Bulk Copy on Partitioned Tables . . . . . . . . . . . . . . . . 2-12Parallel Bulk Copy and IDENTITY Columns . . . . . . . . . . . . . . . . . . 2-12

Using the bcp Options. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-14 Using the Default Formats . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-14

Native Format. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-15Character Format . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-15Changing Terminators. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-16

Changing the Defaults: Interactive bcp . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-16File Storage Type. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-17Prefix Length . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-18Field Length. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-18Field and Row Terminators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-20

Using Format Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-21Elements of the bcp Format File . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-22

Host File Column Order . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-22Host File Datatype . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-23Prefix Length. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-23Host File Data Length . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-24Terminator. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-24Server Column Order . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-24Server Column Name . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-24Column Precision. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-24Column Scale . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-24

Examples: Copying Out Data Interactively. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-25Copying Out Data with Field Lengths . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-25Copying Out Data with Delimiters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-27

Examples: Copying In Data Interactively . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-29Copying In Data with Field Lengths . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-29Copying In Data with Delimiters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-31Copying In Data with a Format File . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-32

Utility Programs for UNIX Platforms vii


Using bcp with Alternate Languages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-32Copy In and Batches . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-33

Batches and Partitioned Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-33Copy Out and text and image Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-34Specifying a Network Packet Size . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-34Copy In and Error Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-34Copy Out and Error Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-35Data Integrity: Defaults, Rules, and Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-35How bcp Differs from Other Utilities . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-36

3. Using the isql UtilityHow to Use Transact-SQL with the isql Utility . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-1

Formatting isql Output. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-2Correcting Input . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-3set Options That Affect Output. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-4

Changing the Command Terminator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-4Performance Statistics Interaction with Command Terminator Values . . . . . . . . . . . . 3-5Setting the Network Packet Size . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-6

Setting the Network Packet Size for the Client . . . . . . . . . . . . . . . . . . . . . 3-6Input and Output Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-6

UNIX Command Line Redirection. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-7

viii


Utility Programs for UNIX Platforms ix

About This Book

Utility Programs for UNIX Platforms is a guide to the Sybase®Adaptive Server Enterprise utility programs available on all UNIXplatforms. Utility programs are commands that you invoke directlyfrom the operating system.

Audience

This manual is for anyone using Transact-SQL® and AdaptiveServer Release 11.5.x. It is a reference manual, and assumes that youhave basic knowledge of how to use the UNIX operating system andAdaptive Server.

How to Use This Book

This manual includes the following chapters:

• Chapter 1, “Utility Commands Reference,” describes the utilitycommands.

• Chapter 2, “Using bcp to Transfer Data to and from AdaptiveServer,” discusses bcp in detail.

• Chapter 3, “Using the isql Utility,” discusses using the interactiveSQL utility.

The examples in this book are based on the pubs2 sample database.Ask your System Administrator how to get a clean copy of pubs2.

Adaptive Server Enterprise Documents

The following documents comprise the Sybase Adaptive ServerEnterprise documentation:

• The Release Bulletin for your platform – contains last-minuteinformation that was too late to be included in the books.

A more recent version of the Release Bulletin may be available onthe World Wide Web. To check for critical product or documentinformation that was added after the release of the product CD,use SyBooks™-on-the-Web.

x

Adaptive Server Enterprise Documents Adaptive Server Enterprise Release 11.5.x

• The Adaptive Server installation documentation for yourplatform – describes installation and upgrade procedures for allAdaptive Server and related Sybase products.

• The Adaptive Server configuration documentation for yourplatform – describes configuring a server, creating networkconnections, configuring for optional functionality, such asauditing, installing most optional system databases, andperforming operating system administration tasks.

• What’s New in Adaptive Server Enterprise? – describes the newfeatures in Adaptive Server release 11.5, the system changesadded to support those features, and the changes that may affectyour existing applications.

• Navigating the Documentation for Adaptive Server – an electronicinterface for using Adaptive Server. This online documentprovides links to the concepts and syntax in the documentationthat are relevant to each task.

• Transact-SQL User’s Guide – documents Transact-SQL, Sybase’senhanced version of the relational database language. Thismanual serves as a textbook for beginning users of the databasemanagement system. This manual also contains descriptions ofthe pubs2 and pubs3 sample databases.

• System Administration Guide – provides in-depth informationabout administering servers and databases. This manual includesinstructions and guidelines for managing physical resources anduser and system databases, and specifying character conversion,international language, and sort order settings.

• Adaptive Server Reference Manual – contains detailed informationabout all Transact-SQL commands, functions, procedures, anddatatypes. This manual also contains a list of the Transact-SQLreserved words and definitions of system tables.

• Performance and Tuning Guide – explains how to tune AdaptiveServer for maximum performance. This manual includesinformation about database design issues that affectperformance, query optimization, how to tune Adaptive Serverfor very large databases, disk and cache issues, and the effects oflocking and cursors on performance.

• Security Features User’s Guide – provides instructions andguidelines for using the security options provided in AdaptiveServer from the perspective of the non-administrative user.

Utility Programs for UNIX Platforms xi

Adaptive Server Enterprise Release 11.5.x Other Sources of Information

• Error Messages and Troubleshooting Guide – explains how to resolvefrequently occurring error messages and describes solutions tosystem problems frequently encountered by users.

• Component Integration Services User’s Guide for Adaptive ServerEnterprise and OmniConnect – explains how to use the AdaptiveServer Component Integration Services feature to connect remoteSybase and non-Sybase databases.

• Adaptive Server Glossary – defines technical terms used in theAdaptive Server documentation.

• Master Index for Adaptive Server Publications – combines theindexes of the Adaptive Server Reference Manual, ComponentIntegration Services User’s Guide, Performance and Tuning Guide,Security Administration Guide, Security Features User’s Guide,System Administration Guide, and Transact-SQL User’s Guide.

Other Sources of Information

Use the SyBooks™ and SyBooks-on-the-Web online resources tolearn more about your product:

• SyBooks documentation is on the CD that comes with yoursoftware. The DynaText browser, also included on the CD, allowsyou to access technical information about your product in aneasy-to-use format.

Refer to Installing SyBooks in your documentation package forinstructions on installing and starting SyBooks.

• SyBooks-on-the-Web is an HTML version of SyBooks that youcan access using a standard Web browser.

To use SyBooks-on-the-Web, go to http://www.sybase.com, andchoose Documentation.

Conventions Used in This Manual

The following paragraphs detail the typographic conventions usedin this manual.

xii

Conventions Used in This Manual Adaptive Server Enterprise Release 11.5.x

Font and Syntax Conventions

The font and syntax conventions in this reference are as follows:

• Syntax statements (displaying the syntax and all options for acommand) appear as follows:

showserver parameter

or, for a command with more options:

Element Example

Command names, command optionnames, utility names, utility flags, andother keywords are bold.

select

Database names, datatypes, file namesand path names are in italics.

master database

Variables, or words that stand for valuesthat you fill in, are in italics.

selectfrom table_namewhere search_conditions

Parentheses are to be typed as part ofthe command.

compute row_aggregate ( column_name )

Curly braces indicate that you mustchoose at least one of the enclosedoptions. Do not type the braces.

{cash, check, credit}

Brackets mean choosing one or more ofthe enclosed options is optional. Do nottype the brackets.

[anchovies]

The vertical bar means you may selectonly one of the options shown.

{die_on_your_feet | live_on_your_knees |live_on_your_feet}

The comma means you may choose asmany of the options shown as you like,separating your choices with commas tobe typed as part of the command.

[extra_cheese, avocados, sour_cream]

An ellipsis (...) means that you canrepeat the last unit as many times as youlike.

buy thing = price [cash | check | credit] [, thing = price [cash | check | credit]]...

You must buy at least one thing and give its price. You maychoose a method of payment: one of the items enclosed insquare brackets. You may also choose to buy additionalthings: as many of them as you like. For each thing you buy,give its name, its price, and (optionally) a method ofpayment.

Utility Programs for UNIX Platforms xiii

Adaptive Server Enterprise Release 11.5.x If You Need Help

buildmaster [-d physicalname ] [-c cntrltype ][-s ] [-m]

In syntax statements, commands are in normal font and optionsare in lowercase: normal font for flags, italics for user-suppliedvalues.

• Examples showing utility commands appear in bold, as follows:

bcp -v

• Examples of output from the computer appear as follows:

pub_id pub_name city state------- ------------------- ----------- -----0736 New Age Books Boston MA0877 Binnet & Hardley Washington DC1389 Algodata Infosystems Berkeley CA

(3 rows affected)

If You Need Help

Each Sybase installation that has purchased a support contract hasone or more designated people who are authorized to contact SybaseTechnical Support. If you cannot resolve a problem using themanuals or online help, please have the designated person contactSybase Technical Support or the Sybase subsidiary in your area.

xiv

If You Need Help Adaptive Server Enterprise Release 11.5.x

Utility Programs for UNIX Platforms xv

List of Tables

Table 1-1: Utility programs for UNIX ...........................................................................................1-2Table 1-2: Default character sets for different platforms..........................................................1-11Table 1-3: bcp prompts—their defaults and user responses ...................................................1-19Table 1-4: Adaptive Server char data..........................................................................................1-20Table 1-5: Other datatypes converted to char storage..............................................................1-21Table 1-6: Default character sets for different platforms..........................................................1-39Table 1-7: Summary of sybload prompts for tape installation................................................1-61Table 2-1: Characteristics of fast and slow bcp............................................................................2-3Table 2-2: Fast and slow bcp with select into/bulkcopy/pllsort..............................................2-5Table 2-3: Steps for copying data using fast bcp..........................................................................2-6Table 2-4: File storage datatypes for bcp ....................................................................................2-17Table 2-5: Default field lengths for datatypes............................................................................2-19Table 2-6: Host file datatype storage format..............................................................................2-23Table 2-7: Allowable prefix length values..................................................................................2-23Table 3-1: Format options for isql..................................................................................................3-2Table 3-2: set options that affect Transact-SQL output...............................................................3-4

xvi List of Tables


Utility Programs for UNIX Platforms 1-1

1 Utility Commands Reference 1.

This chapter contains reference pages for the following utilityprogram commands.

• backupserver 1-4

• bcp 1-7

• buildmaster 1-23

• dataserver 1-26

• defncopy 1-29

• dscp 1-34

• dsedit 1-35

• isql 1-36

• langinstall 1-44

• showserver 1-47

• sqlloc 1-48

• sqllocres 1-50

• sqlupgrade 1-52

• sqlupgraderes 1-53

• srvbuild 1-54

• srvbuildres 1-56

• startserver 1-58

• sybload 1-61

• sybsetup 1-63

How the UNIX Shell Interprets Utility Program Commands

The UNIX system shell interprets the utility program commands.Place characters with special meaning to the shell, such as thebackslash (\), asterisk (*), slash (/), and spaces, in quotes. You canprecede some special characters with the backslash (\) to “escape”them. This prevents the shell from interpreting the special characters.

1-2 Utility Commands Reference

How the UNIX Shell Interprets Utility Program Commands Adaptive Server Enterprise Release 11.5.x

The following table lists the utility programs available for UNIXplatforms:

Table 1-1: Utility programs for UNIX

Utility Description

backupserver Executable form of the Backup Server program.

bcp Copies rows in a database table to or from an operatingsystem file in a user-specified format.

buildmaster Builds the master device and creates the master, model, andtempdb databases on the device.

dataserver Executable form of the Adaptive Server program.

defncopy Copies definitions for specified views, rules, defaults,triggers, procedures, or reports from a database to anoperating system file or from an operating system file to adatabase.

dscp Allows you to view and edit server entries in the interfacesfile from the command line.

dsedit Allows you to view and edit server entries in the interfacesfile using a graphical user interface based on X11/Motif.

isql Interactive SQL parser to Adaptive Server.

langinstall Installs a new language on the Adaptive Server.

showserver Shows Adaptive Servers and Backup Servers that arecurrently running on the local machine.

sqlloc Installs and modifies languages, character sets, and sortorder defaults for Adaptive Server in GUI mode.

sqllocres Installs and modifies languages, character sets, and sortorder defaults for Adaptive Server in command line mode.

sqlupgrade Upgrades your currently installed release of AdaptiveServer to the newest release in GUI mode.

sqlupgraderes Upgrades your currently installed release of AdaptiveServer to the newest release in command line mode.

srvbuild Creates a new Adaptive Server, Backup Server, MonitorServer, or XP Server in GUI mode with default or user-specified values for key configuration attributes.

srvbuildres Creates a new Adaptive Server, Backup Server, MonitorServer, or XP Server in command line mode with default oruser-specified values for key configuration attributes.

startserver Starts an Adaptive Server or a Backup Server.


Adaptive Server Enterprise Release 11.5.x How the UNIX Shell Interprets Utility Program Commands

sybload Uploads Sybase products from the distribution media andbuilds the Sybase installation directory from the commandline.

sybsetup Installs and configures Adaptive Server from a singlelocation using a GUI interface.

Table 1-1: Utility programs for UNIX

Utility Description


backupserver Adaptive Server Enterprise Release 11.5.x

backupserver

Function

The executable form of the Backup Server program.

Syntax

backupserver[-C server_connections ][-S b_servername ][-I interfaces_file ][-e error_log_file ][-M sybmultbuf_binary ][-N network_connections ][-T trace_value ][-L Sybase_language_name ][-J Sybase_character_set_name ][-c tape_config_file ]

or

backupserver -v

Parameters

-C server_connections – specifies the number of server connections forthe Backup Server. The Backup Server requires:

- Two connections for each dump session.

- One connection for each load session.

- One connection for volume change messages.

Allow a maximum of three times the number of expectedconcurrent dump and load sessions. The default value is 30server connections.

-S b_servername – specifies the name of the Backup Server to start. Thedefault is SYB_BACKUP. This entry must specify the name of aBackup Server in the interfaces file.

-I interfaces_file – specifies the name and location of the interfaces fileto search when connecting to Backup Server. If -I is omitted,backupserver looks for a file named interfaces in the directorypointed to by your SYBASE environment variable.

-e error_log_file – specifies the name and location of the Backup Servererror log file used to report Open Server internal errors,


Adaptive Server Enterprise Release 11.5.x backupserver

sybmultbuf errors, errors that halt the Backup Server, and errors fordisconnected sessions. All other errors are sent to the notifydestination specified in the dump database, dump transaction, loaddatabase, and load transaction commands.

-M sybmultbuf_binary – specifies the full path name of the sybmultbufexecutable. Use this parameter only when starting Backup Serverfrom a directory other than the bin directory of the Sybaseinstallation directory, or when using a diagnostic version ofsybmultbuf.

-N network_connections – specifies the number of total networkconnections (DBPROCESSes) that the master Backup Server canoriginate. The default value is 25.

-T trace_value – interprets trace_value as a bit mask (base-2 number).The 1 bits in trace_value correspond to Open Server trace flags toturn on. If you specify more than one -T parameter on thecommand line, the final -T value overrides the values from earlier-T parameters. The trace_value must be a positive integer.

-L Sybase_language_name – specifies the default language for BackupServer. If not specified, Backup Server uses the locale specified bythe LC_ALL or LANG environment variables. If these variablesare not set, Backup Server searches for the “default” entry inlocales.dat.

-J Sybase_character_set_name – specifies the default character set forBackup Server.

-c tape_config_file – specifies the name and location of the tapeconfiguration file to search for tape device configurationinformation before doing a dump database or a dump transaction. Ifyou do not specify -c, the default path name for the tapeconfiguration file is $SYBASE/backup_tape.cfg.

-v – prints the version number and copyright message of thebackupserver software and then exits.

Comments

• Start Backup Server with the startserver command rather than bydirectly executing the backupserver program. To change defaultvalues, edit the RUN_servername file in your Sybase installationdirectory. See the startserver reference page for details.

• If you do not specify a Backup Server name with the -S parameter,and you have not set the environment variable DSLISTEN,


backupserver Adaptive Server Enterprise Release 11.5.x

backupserver uses the default Backup Server name SYB_BACKUP.The value of the DSLISTEN environment variable overrides thisdefault value, and the -S parameter overrides both the defaultand the value specified in DSLISTEN.

• Whenever possible, the Backup Server and any Adaptive Serversthat dump or load directly through the Backup Server shouldshare the same interfaces file. The interfaces file that BackupServer uses must contain entries for:

- Backup Server

- Any other Backup Servers with which this Backup Servercommunicates

• Trace flags cause the Backup Server to print informationregarding its operation while it is running, for debuggingproblems in the Backup Server. See the Open Server Server-Library/C Reference Manual for more details on trace flags. InUNIX, the Backup Server does not support use of the OpenServer-defined SRV__TR symbols for -T.

• If Backup Server cannot find the locales and charsets directoriesspecified by the -L and -J parameters, or if these parametersspecify an incorrect language and character set combination,Backup Server issues an error message and uses the defaultlanguage and character set.


Adaptive Server Enterprise Release 11.5.x bcp

bcp

Function

Copies a database table to or from an operating system file in a user-specified format.

Syntax

bcp [[ database_name .] owner .][ view_name | table_name[: partition_id ]] {in | out} datafile[-c] [-E] [-n] [-N] [-X][-a display_charset ][-A packet_size ][-b batchsize ][-e errfile ][-f formatfile ][-F firstrow ][-g id_start_value ][-I interfaces_file ][-J client_charset ][-L lastrow ][-m maxerrors ][-P password ][-q datafile_charset ][-r row_terminator ][-R remote_server_principal ][-S server ][-t field_terminator ][-T text_or_image_size ][-U username ][-z language ]

or

bcp -v

Parameters

database_name – is optional if the table being copied is in your defaultdatabase or in master. Otherwise, you must specify a databasename.

owner – is optional if you or the Database Owner own the table beingcopied. If you do not specify an owner, bcp first looks for a table ofthat name that you own, and then looks for one owned by the


bcp Adaptive Server Enterprise Release 11.5.x

Database Owner. If another user owns the table, you must specifythe owner name or the command fails.

view_name – is the name of the view you are copying out.

table_name – is the name of the database table to copy. The table namecannot be a Transact-SQL reserved word.

: partition_id – is the identifier of the partition into which to copy.

in | out – is the direction of the copy. in indicates a copy from a file intothe database table; out indicates a copy to a file from the databasetable or view.

datafile – is the full path name of an operating system file. The pathname can be from 1–255 characters in length.

-c – performs the copy operation with char datatype as the defaultstorage type of all columns in the data file. Use this format if youare sharing data between platforms. This parameter does notprompt for each field; it uses char as the default storage type, noprefixes, \t (tab) as the default field terminator, and \n (newline)as the default row terminator.

-E – explicitly specifies values for a table’s IDENTITY column.

By default, when you bulk copy data into a table with anIDENTITY column, bcp assigns each row a temporary IDENTITYcolumn value of 0. This is effective only when copying data intoa table. bcp reads the value of the ID column from the data file,but does not send it to the server. Instead, as bcp inserts each rowinto the table, the server assigns the row a unique, sequential,IDENTITY column value, beginning with the value 1. If youspecify the -E flag when copying data into a table, bcp reads thevalue from the data file and sends it to the server which insertsthe value into the table. If the number of rows inserted exceedsthe maximum possible IDENTITY column value, AdaptiveServer returns an error.

The -E parameter has no effect when you are bulk copying dataout. Adaptive Server copies the ID column to the data file, unlessyou use the -N parameter.



-n – performs the copy operation using native (operating system)formats. Specifying the -n parameter means bcp will not promptfor each field. Files in native data format are not human-readable.

◆ WARNING!Do not use bcp in native format for data recovery or salvage or toresolve an emergency situation. Do not use bcp in native format totransport data between different hardware platforms, differentoperating systems, or different major releases of Adaptive Server.Using bcp in native format can create flat files that cannot be reloadedinto Adaptive Server and it may be impossible to recover the data. Ifyou are unable to rerun bcp in character format (for example, a tablewas truncated or dropped, hardware damage occurred, a databasewas dropped, and so on,) the data will be unrecoverable.

-N – skips the IDENTITY column. Use this parameter when copyingdata in if your host data file does not include a placeholder for theIDENTITY column values, or when copying data out, if you donot want to include the IDENTITY column information in the hostfile.

You cannot use both -N and -E parameters when copying data in.

-X – specifies that, in this connection to the server, the applicationinitiates the login with client-side password encryption. bcp (theclient) specifies to the server that password encryption is desired.The server sends back an encryption key, which bcp uses toencrypt your password. The server uses the key to authenticateyour password when it arrives.

If bcp crashes, the system creates a core file that contains yourpassword. If you did not use the encryption option, thepassword appears in plain text in the file. If you used theencryption option, your password is not readable.

-a display_charset – allows you to run bcp from a terminal where thecharacter set differs from that of the machine on which bcp isrunning. Use -a in conjunction with -J to specify the character settranslation file (.xlt file) required for the conversion. Use -awithout -J only if the client character set is the same as the defaultcharacter set.

-A packet_size – specifies the network packet size to use for this bcpsession. For example:



bcp pubs2..titles out table_out -A 2048

sets the packet size to 2048 bytes for this bcp session. size must bebetween the values of the default network packet size and maximumnetwork packet size configuration variables, and it must be amultiple of 512.

Use network packet sizes larger than the default to improve theperformance of large bulk-copy operations.

-b batchsize – is the number of rows per batch of data copied (thedefault is to copy all the rows in one batch). Batching applies onlywhen you are bulk copying in; it has no effect on bulk copyingout.

➤ NoteSetting the batch size to 1 causes Adaptive Server to allocate one data

page to each row copied in. This option only applies to fast bcp, and is only

useful in locating corrupt rows of data.

-e errfile – is the full path name of an error file where bcp stores anyrows that it was unable to transfer from the file to the database.Error messages from the bcp program appear on your terminal.bcp creates an error file only when you specify this parameter.

-f format_file – is the full path name of a file with stored responsesfrom a previous use of bcp on the same table. After you answerbcp’s format questions, it asks you if you want to save youranswers in a format file; creation of the format file is optional. Thedefault file name is bcp.fmt. The bcp program can refer to a formatfile when copying data, so that you do not have to duplicate yourprevious format responses interactively. Use the -f parameter onlyif you previously created a format file that you want to use nowfor a copy in or out. If you do not specify this parameter, bcpqueries you for format information interactively.

-F firstrow – is the number of the first row to copy from an input file(default is the first row).

-g id_start_value – specifies the value of the IDENTITY column to useas a starting point for copying data in.

-I interfaces_file – specifies the name and location of the interfaces fileto search when connecting to Adaptive Server. If you do not



specify -I, bcp looks for an interfaces file located in the directoryspecified by the SYBASE environment variable.

-J client_charset – specifies the character set to use on the client. bcpuses a filter to convert input between client_charset and theAdaptive Server character set.

-J client_charset requests that Adaptive Server convert to andfrom client_charset, the character set used on the client.

-J with no argument sets character-set conversion to NULL. Noconversion takes place. Use this if the client and server use thesame character set.

Omitting -J sets the character set to a default for the platform.Table 1-2 lists platform defaults.

For more information about character sets and associated flags,see the System Administration Guide.

-L lastrow – is the number of the last row to copy from an input file(default is the last row).

-m maxerrors – is the maximum number of nonfatal errors permittedbefore bcp aborts the copy. bcp discards each row that it cannotinsert (due to a data conversion error, or an attempt to insert anull value into a column that does not allow them), counting eachrejected row as one error. If you do not include this parameter, bcpuses a default value of 10.

-P password – specifies an Adaptive Server password. If you do notspecify -P password, bcp prompts for a password. You can leave outthe -P flag If your password is NULL.

Table 1-2: Default character sets for different platforms

Platform Default Character Set

Sun Solaris, Digital UNIX, NCR, RS/6000 iso_1

HP-UX roman8

OS/2, Novell NetWare 386 cp850

Macintosh mac



➤ NoteThe utility programs described in Table 1-1 on page 1-2 may allow you to

use a -P parameter to enter your password. If security is an issue, do not

use this parameter to specify your password. Another user may have an

opportunity to see it. Instead, log in as usual without the -P parameter, and

let Adaptive Server prompt you for your password.

-q datafile_charset – runs bcp to copy character data to or from a filesystem that uses a character set different from the client characterset. Use -q in conjunction with -J to specify the character settranslation file (.xlt file) required for the conversion.

In Japanese language environments, the -q flag translatesHankaku Katakana (half-width characters) into ZenkakuKatakana (full-width characters). Use with the argument zenkakuand with the -J flag to indicate the client’s Japanese character set(sjis or eucjis). The zenkaku.xlt file translates only from theterminal to Adaptive Server, not from Adaptive Server to theterminal.

➤ NoteThe ascii_7 character set is compatible with all character sets. If either the

Adaptive Server character set or the client character set is set to ascii_7,

any 7-bit ASCII character is allowed to pass between client and server

unaltered. Other characters produce conversion errors. Character set

conversion issues are covered more thoroughly in “Configuring

Client/Server Character Set Conversions” in the System AdministrationGuide.

-r row_terminator – specifies the row terminator.

➤ NoteWhen specifying terminators from the command line with the -t or -rparameter, you must escape characters that have special significance to

the UNIX operating system (see example 1 on page 1-13). Either place a

backslash in front of the special character or enclose it in quotes. This is not

necessary when bcp prompts you (interactive mode).



-R remote_server_principal – specifies the principal name for theremote server. By default, a server’s principal name matches theserver’s network name (which is specified with the -S parameteror the DSQUERY environment variable). Use the -R parameterwhen the server’s principal name and network name are not thesame.

-S server – specifies the name of the Adaptive Server to which toconnect. If you specify -S with no argument, bcp uses the serverthat your DSQUERY environment variable specifies.

-t field_terminator – specifies the field terminator.

-T text_or_image_size – allows you to specify, in bytes, the maximumlength of text or image data that Adaptive Server sends. Thedefault is 32K. If a text or image field is larger than the value of -Tor the default, bcp does not send the overflow.

-U username – specifies an Adaptive Server login name. If you do notspecify username, bcp uses the current user’s operating systemlogin name.

-z language – is the official name of an alternate language that theserver uses for date formats and to display bcp prompts andmessages. Without the -z flag, bcp uses the server’s defaultlanguage. You can add languages to an Adaptive Server duringinstallation or afterwards, using either the langinstall utility or thesp_addlanguage stored procedure.

-v – displays the version number of bcp and a copyright message andreturns to the operating system.

Examples

1. bcp pubs2..publishers out pub_out -c -t , -r \\r

Copies data out of the publishers table. The -c parameter specifiescharacter format (using char for all fields). The -t field_terminatorparameter ends each field with a comma, and the -r row_terminator parameter ends each line with a Return. bcpprompts only for a password. The first backslash before the final“r” escapes the second so that only one backslash is printed.

2. bcp pubs2..publishers out pub_out



Password:

Enter the file storage type of field pub_id [char]:Enter prefix length of field pub_id [0]:Enter length of field pub_id [4]:Enter field terminator [none]:

Enter the file storage type of field pub_name [char]:Enter prefix length of field pub_name [1]:Enter length of field pub_name [40]:Enter field terminator [none]:

Enter the file storage type of field city [char]:Enter prefix length of field city [1]:Enter length of field city [20]:Enter field terminator [none]:

Enter the file storage type of field state [char]:Enter prefix length of field state [1]:Enter length of field state [2]:Enter field terminator [none]:

Do you want to save this format information in a file? [Y-n] yHost filename [bcp.fmt]: pub_form

Starting copy...

3 rows copied.Clock Time (ms.): total = 1 Avg = 0 (3000.00 rows per sec.)

Copies data from the publishers table to a file named pub_out forreloading later into Adaptive Server. Press Return to accept thedefaults specified by the prompts. The same prompts appearwhen you copy data into the publishers table.

3. bcp pubs2..publishers in pub_out -f pub_form

Copies data back into Adaptive Server using the saved formatfile, pub_form.

4. To see examples of datatypes, enter “?” at the prompt:



Enter the file storage type of field ’pub_id’[’char’]: ?Invalid column type. Valid types are:<cr>: same type as Adaptive Server column. c : char T : text i : int s : smallint t : tinyint f : float m : money b : bit d : datetime x : binary I : image D : smalldatetime r : real M : smallmoney n : numeric e : decimal

Enter the single letter exactly as it appears above.



5. bcp pubs2..publishers in vt200_data -J iso_1 -qvt200 -z french

Copies a data file created with a character set used on a VT200terminal into the pubs2..publishers table. The -q flag translates it.The -z flag displays bcp messages in French.

6. bcp pubs2..publishers in -a mac -J roman8 -q iso_1

Specifies that you are using a Macintosh, running bcp on aworkstation that is using roman8, with the file system onanother machine that uses iso_1.

7. bcp pubs2..publishers out -T 40960 -A 4096

Specifies that Adaptive Server send 40K of text or image datausing a packet size of 4096 bytes.

Comments

• See Chapter 2, “Using bcp to Transfer Data to and from AdaptiveServer,” for an in-depth discussion of bcp.

• The current version of bcp ignores the -y sybase_directoryparameter.

• bcp provides a convenient, high-speed method for transferringdata between a database table or view and an operating systemfile. bcp can read or write files in a wide variety of formats. Whencopying in from a file, bcp appends data to an existing databasetable; when copying out to a file, bcp overwrites any previouscontents of the file.

• You cannot copy into a view from an operating system file.

• Upon completion, bcp informs you of the number of rows of datasuccessfully copied, the number of rows (if any) that it could notcopy, the total time the copy took, the average amount of time, inmilliseconds, that it took to copy one row and the number of rowscopied per second.

• bcp copies each batch in a single transaction. If Adaptive Serverrejects any row in the batch, the entire transaction is rolled back.By default, bcp copies all rows in a single batch; use the -bparameter to change the default batch size. Adaptive Serverconsiders each batch a single bcp operation, writes each batch to aseparate data page, and continues to the next batch, regardless ofwhether the previous transaction succeeded.



➤ NoteSetting the batch size to 1 causes Adaptive Server to allocate one data

page to each row copied in. This parameter only applies to fast bcp, and is

only useful in locating corrupt rows of data.

• When copying data into a table, bcp observes any defaults definedfor columns and user-defined datatypes. However, bcp ignoresrules and triggers in order to load data at the fastest possiblespeed.

• When you send host data files to sites using terminals that aredifferent from your own, inform them of the datafile_charset thatyou used to create the files.

Fast and Slow bcp

• The bcp program is optimized to load data into tables that do nothave indexes or triggers associated with them. It loads data intotables without indexes or triggers at the fastest possible speed,with a minimum of logging. Page allocations are logged, but theinsertion of rows is not.

When you copy data into a table that has one or more indexes ortriggers, a slower version of bcp is automatically used, whichlogs row inserts. This includes indexes implicitly created usingthe unique integrity constraint of a create table statement.However, bcp does not enforce the other integrity constraintsdefined for a table.

• Because changes to data are not logged when using fast bcp, youmust use dump database to back up your databases. You cannot usedump transaction after a fast bulk copy because no transactions areadded to the transaction log.

Because the fast version of bcp inserts data without logging it, theSystem Administrator or Database Owner must first set thesystem procedure sp_dboption, "DB", to true. If the option is not setto true, and you try to copy data into a table that has no indexesor triggers, Adaptive Server generates an error message. You donot need to set this option to copy data out to a file or to copydata into a table that contains indexes or triggers.

By default, the select into/bulkcopy/pllsort option is false (off) in newlycreated databases. To change the default situation, turn thisoption on in the model database.

• Fast bcp runs more slowly while a dump database is taking place.



The log can grow very large during slow bcp. After the bulk copycompletes, back up your database with dump database, thentruncate the log with dump transaction. You may want to use trunclog on chkpt or a threshold procedure to perform log dumps.

➤ NoteThe performance penalty for copying data into a table that has indexes or

triggers in place can be severe. If you are copying in a very large number of

rows, it may be faster to drop all the indexes and triggers first with drop index(or alter table for indexes created as a unique constraint) and drop trigger, set

the database option, copy the data into the table, re-create the indexes and

triggers, and then dump the database. Remember to allocate disk space for

the construction of indexes and triggers—for a clustered index, about 1.2

times the amount of space needed for the data, in addition to the space

needed for the data.

Responding to bcp Prompts

When you copy data in or out using the -n (native format) or -c(character format) parameter, bcp prompts you only for yourpassword, unless you supplied it with the -P parameter. If you do notsupply either the -n or -c parameter, bcp prompts you for informationfor each field (column) in the table or view.

• Each prompt displays a default value, in brackets, which you canaccept by pressing Return. The prompts include:

- The file storage type, which can be char or any valid AdaptiveServer datatype

- The prefix length, which is an integer indicating the length inbytes of the following data

- The storage length of the data in the file

- The field terminator, which can be any character string

The row terminator is the field terminator of the last field in thetable or file.

• The bracketed defaults represent reasonable values for thedatatypes of the field in question. For the most efficient use ofspace when copying out to a file:

- Use the default prompts

- Copy all data in the datatypes defined by their table



- Use prefixes as indicated

- Do not use terminators

- Accept the default lengths

Table 1-3 shows the bcp prompts, default user responses, andpossible alternate responses:

➤ NoteBe careful when you copy data from different operating systems because

not all operating systems use the same native datatypes (for example,

copying from NT into a UNIX server).

• A prefix length is a 1-, 2-, or 4-byte integer that represents thelength of each data value in bytes. It immediately precedes thedata value in the host file.

• A field terminator string can be up to 30 characters long. Themost common terminators are a tab (entered as “\t” and used forall columns except the last one), and a newline (entered as “\n”and used for the last field in a row). Other terminators are: “\0”

Table 1-3: bcp prompts—their defaults and user responses

Prompt Default Provided Possible User Response

File StorageType

Use database storage type for most fieldsexcept:

char for varcharbinary for varbinary

char, to create or read a human-readable file; any Adaptive Serverdatatype where implicit conversion issupported.

Prefix Length 0 for fields defined with char datatype (notstorage type) and all fixed-length datatypes

1 for most other datatypes

2 for binary and varbinary saved as char

4 for text and image

0 if no prefix is desired; otherwise,defaults are recommended.

Storage Length For char and varchar, use defined length.For binary and varbinary saved as char, usedouble the defined length.For all other datatypes, use maximumlength needed to avoid truncation or dataoverflow.

Default values, or greater, arerecommended.

Field or RowTerminator

None Up to 30 characters



(the null terminator), “\” (backslash), and “\r” (Return). Whenchoosing a terminator, be sure that its pattern does not appear inany of your character data, because bcp always looks for the firstpossible terminator.

A terminator or prefix affects the actual length of datatransferred:

- If the length of an entry being copied out to a file is less than thestorage length (other than char, nchar, and binary data), it isfollowed immediately by the terminator or the prefix for thenext field, and not padded to the full storage length.

- When copying in, bcp copies data until it either reaches thenumber of bytes indicated in the “Length” prompt orencounters the terminator. If the data exceeds the specifiedlength, bcp flushes the data until it finds the terminator.Without terminators, the table storage length is strictlyobserved.

• Fields stored as char (except char, nchar, and binary fields) insteadof their database datatypes take less file storage space with thedefault length and prefix or a terminator. bcp can use either aterminator or a prefix to determine the most efficient use ofstorage space. bcp suggests the maximum amount of storagespace required for each field as the default. For char or varchardata, bcp accepts any length.

• Fields defined in the database as char, nchar, and binary, and thosethat do not permit null values, are always padded with spaces(null bytes for binary) to the full length defined in the database.timestamp data is treated as binary(8).

If data in the varchar and varbinary fields is longer than the lengthspecified for copy out, bcp silently truncates the data in the file atthe specified length.

• Table 1-4 and Table 1-5 show the interaction of prefix lengths,terminators, and field lengths on the information in the file. “P”indicates the prefix in the stored table. “T” indicates theterminator, and dashes (--) show appended spaces. An ellipsis (...)indicates that the pattern repeats for each field. The field length is8 for each column, and “string” represents the 6-character fieldeach time.

Table 1-4: Adaptive Server char data

Prefix length = 0 Prefix length = 1, 2, or 4



• File storage length is the maximum amount of data that can betransferred for the column, plus terminators and prefixes.

• bcp considers any data column that can contain null values to bevariable length, so use either a length prefix or terminator todenote the length of each row of data.

Datatypes and Storage Types

• bcp can copy data out to a file either as its database datatype or asany datatype for which implicit conversion is supported for thedatatype in question. bcp copies user-defined datatypes as theirbase datatype or as any datatype for which implicit conversion issupported. For more information on datatype conversions, seedbconvert in the Open Client DB-Library/C Reference Manual and“Datatype Conversion Functions” in the Adaptive Server ReferenceManual.

• The file storage type and length of a column need not be the sameas the type and length of the column in the database table. (Iftypes and formats copied in are incompatible with the structureof the database table, the copy fails.)

• Data written to a host file in its native format preserves all of itsprecision. datetime and float values preserve all of their precisioneven when they are converted to character format. AdaptiveServer stores money values to a precision of one ten-thousandth ofa monetary unit. However, when money values are converted tocharacter format, their character format values are recorded onlyto the nearest two places.

• Before copying data that is in character format from a file into adatabase table, check the datatype entry rules in Chapter 7,

No terminator string--string--... Pstring--Pstring--...

Terminator string--Tstring--T... Pstring--TPstring--T...

Table 1-5: Other datatypes converted to char storage

Prefix length = 0 Prefix length = 1, 2, or 4

No terminator string--string--... PstringPstring...

Terminator stringTstringT... PstringTPstringT...

Table 1-4: Adaptive Server char data



“System and User-Defined Datatypes,” of the Adaptive ServerReference Manual. Character data copied into the database withbcp must conform to those rules. Note especially that dates in theundelimited (yy)yymmdd format may result in overflow errors ifthe year is not specified first.

Messages

• Error in attempting to determine the size of apair of translation tables.:’stat’ utility failed.

The character translation file(s) named with the -a or -qparameter is missing, or you mistyped the name(s).

• You cannot use the -g and -E flags together.

• Partition number partition_number does not existin table table_name .

See Also

Chapter 2, “Using bcp to Transfer Data to and from AdaptiveServer.”


Adaptive Server Enterprise Release 11.5.x buildmaster

buildmaster

Function

Builds the master device and creates the master, model, and tempdbdatabases on the device.

Syntax

buildmaster[-d disk ][-c cno ][-s size ][-m][-r][-q][-x]

or

buildmaster -v

Parameters

-d disk – is the physical name of the raw disk partition or operatingsystem file where the master device resides.

-c cno – is the controller number for the master device. Together, cnoand disk specify the device. The default value for cno is 0. Do notchange this value unless instructed to do so by Sybase TechnicalSupport.

-s size – is the size of the master device in 2K blocks. There are 512 2Kblocks in 1MB. For example, a size of “15360” creates a 30MBmaster device. buildmaster verifies that the value you specify forthis parameter does not exceed the space available to the masterdevice, unless you use the -m parameter. See the installation orconfiguration documentation for your platform for moreinformation about the size of master devices.

-m – rewrites only the master database, without initializing the masterdevice. Use this parameter when the master database is corruptedbut the other databases on the master device are undamaged.

-r mastermirror – starts the mirror of the master device.

-q – does not clear unallocated pages in master and model databases.This parameter has no effect when used with -x.


buildmaster Adaptive Server Enterprise Release 11.5.x

-x – rewrites only the model database, without changing or initializingthe master device. Use this parameter when the model database iscorrupted and you cannot load it successfully from a backup. Ifyou modified model, you must restore it from a backup afterreinitializing it with this parameter.

-v – prints the version number and copyright message for buildmasterand then exits.

Examples

1. buildmaster -d /dev/rsd1f -s8704

Initializes the raw device /dev/rsd1f as a 17MB master device andcreates the system databases master, model, and tempdb on thedevice.

Comments

• The buildmaster program initializes the specified database deviceas an Adaptive Server master device and builds the master andmodel databases on it.

• Use buildmaster only when Adaptive Server is shut down.

• The Adaptive Server installation program runs buildmaster andbuilds an initial master database on the database device youspecify in answer to the program’s prompts.

• If you run buildmaster with no parameters, it prompts for thefollowing information:

master disk name?master disk controller number?master disk size?configuration only? (y or n)databases only? (y or n)

You must enter a response for each prompt.

Answer “n” to “configuration only?”, and use the followingcommand to restore the configuration settings:

sp_configure "configuration file", 0, restore, file_name

Answering “yes” to the “databases only?” prompt is the same asrunning buildmaster with the -m flag.

• For information on backing up and recovering system databases,see Chapter 20, “Developing a Backup and Recovery Plan,” andChapter 22, “Restoring the System Databases,” in the SystemAdministration Guide.


Adaptive Server Enterprise Release 11.5.x buildmaster

• The password to the default “sa” account reverts to null after yourun buildmaster -m, and the account is unlocked. Loading a backupof the master database restores the “sa” password and accountlock state to what they were when the dump was taken.

See Also

Utilities startserver


dataserver Adaptive Server Enterprise Release 11.5.x

dataserver

Function

The executable form of the Adaptive Server program.

Syntax

dataserver -d devicename [-c configurationfile ][-e errorlogfile ][-m][-r mastermirror ][-M sharedmem_directory ][-i interfaces_file_directory ][-s servername ][-p sso_login_name ]

or

dataserver -v

Parameters

-d devicename – is the full path name of the device for the masterdatabase. The master database device must be writable by theuser who starts Adaptive Server. The default master databasedevice name is d_master.

-c configurationfile – specifies the full path name of an Adaptive Serverconfiguration file. Use this parameter to start Adaptive Serverwith the configuration values in the specified configuration file.See “Starting Adaptive Server with a Configuration File” inChapter 11, “Setting Configuration Parameters” in the SystemAdministration Guide for further information on using thisparameter.

-e errorlogfile – is the full path name of the error log file for AdaptiveServer system-level error messages.

-m – starts Adaptive Server in single-user mode.

-r mastermirror – starts the mirror of the master device. Use thisparameter to start Adaptive Server if the master device has beendamaged.

-M sharedmem_directory – places shared memory files in the specifieddirectory instead of in the default location, $SYBASE. Ifsharedmem_directory starts with “/”, the directory name is


Adaptive Server Enterprise Release 11.5.x dataserver

assumed to be absolute. Otherwise, the directory name isinterpreted relative to $SYBASE.

-i interfaces_file_directory – specifies the directory location of theinterfaces file to search when connecting Adaptive Server. If -I isomitted, dataserver looks for a file named interfaces in the directorypointed to by your SYBASE environment variable.

-s servername – specifies the name of the Adaptive Server to start. If -sis omitted, a server named SYBASE is started.

-p sso_login_name – specifies the login name of a System SecurityOfficer when starting Adaptive Server, for the purposes ofgetting a new password for that account. Adaptive Servergenerates a random password, displays it, encrypts it, and savesit in master..syslogins as that account’s new password.

-v – prints the version number and copyright message for dataserverand then exits.

Comments

• Start Adaptive Server with the startserver command rather than bydirectly executing the dataserver program. If you need to changeany of the default values, edit the RUN_servername file in yourSybase installation directory. See the startserver reference page fordetails.

• When you start an Adaptive Server with the dataserver program,Adaptive Server derives its running environment from:

- the configuration file you specify in -c configurationfile;

- the default configuration file, servername.cfg, if you did notspecify the -c parameter;

- default values if you did not specify either -c configurationfile orservername.cfg.

• For more information on these configuration parameters, seeChapter 11, “Setting Configuration Parameters,” in the SystemAdministration Guide.

• Because Adaptive Server passwords are encrypted, you cannotrecover forgotten passwords. If all System Security Officers losetheir passwords, the -p parameter generates a new password for aSystem Security Officer account. Start Adaptive Server with -p,immediately log into Adaptive Server with the new randompassword, and execute sp_password to reset your password to amore secure one.


dataserver Adaptive Server Enterprise Release 11.5.x

• After you have finished running the Adaptive Server installationprogram, be sure to set the file permissions on the dataserverexecutable to limit who can execute it.

• If you do not specify an Adaptive Server name with the -sparameter, and you have not set the DSLISTEN environmentvariable, dataserver uses the default Adaptive Server nameSYBASE. The value of the DSLISTEN environment variableoverrides this default value, and the -s parameter overrides boththe default and the DSLISTEN environment variable.


Adaptive Server Enterprise Release 11.5.x defncopy

defncopy

Function

Copies definitions for specified views, rules, defaults, triggers, orprocedures from a database to an operating system file or from anoperating system file to a database.

➤ NoteThe defncopy utility cannot copy table definitions or reports created with

Report Workbench™.

Syntax

defncopy[-X][-a display_charset ][-I interfaces_file ][-J [ client_charset ]][-P password ][-R remote_server_principal ][-S [ server ]][-U username ][-z language ]{in filename dbname | out filename dbname[ owner .] objectname [[ owner .] objectname ...] }

or

defncopy -v

Parameters

-X – initiates the login with client-side password encryption in thisconnection to the server. defncopy (the client) specifies to the serverthat password encryption is desired. The server sends back anencryption key, which defncopy uses to encrypt your password,and the server uses the key to authenticate your password whenit arrives.

If defncopy crashes, the system creates a core file which containsyour password. If you did not use the encryption option, thepassword appears in plain text in the file. If you used theencryption option, your password is not readable.


defncopy Adaptive Server Enterprise Release 11.5.x

-a display_charset – runs defncopy from a terminal whose character setdiffers from that of the machine on which defncopy is running. Use-a in conjunction with -J to specify the character set translation file(.xlt file) required for the conversion. Use -a without -J only if theclient character set is the same as the default character set.



any 7-bit ASCII character can pass unaltered between client and server.

Other characters produce conversion errors. For more information on

character set conversion, see the System Administration Guide.

-I interfaces_file – specifies the name and location of the interfaces fileto search when connecting to Adaptive Server. If you do notspecify -I, defncopy looks for a file named interfaces in the directoryspecified by the SYBASE environment variable.

-J client_charset – specifies the character set to use on the client. Afilter converts input between client_charset and the AdaptiveServer character set.

-J client_charset requests that Adaptive Server convert to andfrom client_charset, the client’s character set.

-J with no argument sets character set conversion to NULL. Noconversion takes place. Use this if the client and server are usingthe same character set.

Omitting -J sets the character set to a default for the platform.The default may not be the character set that the client is using.See Chapter 14, “Configuring Client/Server Character SetConversions,” in the System Administration Guide for moreinformation about character sets and their associated flags.

-P password – specifies your password. If you do not specify -P,defncopy prompts for your password.

-R remote_server_principal – specifies the principal name for the server.By default, a server’s principal name matches the server’snetwork name (which is specified with the -S parameter or theDSQUERY environment variable). Use the -R parameter when theserver’s principal name and network name are not the same.



-S server – specifies the name of the Adaptive Server to which toconnect. If you specify -S with no argument, defncopy looks for aserver named SYBASE. If you do not specify -S, defncopy uses theserver specified by your DSQUERY environment variable.

-U username – specifies a login name. Login names are case sensitive.If you do not specify username, defncopy uses the current user’soperating system login name.

-z language – is the official name of an alternate language that theserver uses to display defncopy prompts and messages. Withoutthe -z flag, defncopy uses the server’s default language. Addlanguages to an Adaptive Server at installation, or afterwardswith the utility langinstall or the stored procedure sp_addlanguage.

in | out – specifies the direction of definition copy.

filename – specifies the name of the operating system file destinationor source for the definition copy. The copy out overwrites anyexisting file.

dbname – specifies the name of the database to copy the definitionsfrom or to.

owner – is optional if you or the Database Owner own the table beingcopied. If you do not specify an owner, defncopy first looks for atable of that name that you own, and then looks for one owned bythe Database Owner. If another user owns the table, you mustspecify the owner name or the command fails.

objectname – specifies name(s) of database object(s) for defncopy tocopy out. Do not use objectname when copying definitions in.

-v – displays the version and copyright message of defncopy andreturns to the operating system.

Comments

• Invoke the defncopy program directly from the operating system.defncopy provides a non-interactive way of copying outdefinitions (create statements) for views, rules, defaults, triggers,or procedures from a database to an operating system file.Alternatively, it copies in all the definitions from a specified file.

• You must have select permission on the sysobjects and syscommentstables to copy out definitions; you do not need permission on theobject itself.


defncopy Adaptive Server Enterprise Release 11.5.x

You may not have select permission on the text column of thesyscomments table if the System Security Officer has reset the allowselect on syscomments.text column parameter with the systemprocedure sp_configure. This reset restricts select permission to theobject owner and the System Administrator. This restriction isrequired in order to run Adaptive Server in the evaluatedconfiguration, as described in the installation and configurationdocumentation for your platform. In this case, the object owneror a System Administrator must execute defncopy to copy outdefinitions.

➤ NoteIf the text has been encrypted, it may be hidden from you even if you have

all the required permissions. For more information, see “Verifying and

Encrypting Source Text” on page 1-4 of the Transact SQL User’s Guide.

• You must have the appropriate create permission for the type ofobject you are copying in. Objects copied in belong to the copier.A System Administrator copying in definitions on behalf of auser must log in as that user to give the user proper access to thereconstructed database objects.

• The in filename or out filename and the database name are requiredand must be stated unambiguously. For copying out, use filenames that reflect both the object’s name and its owner.

• defncopy ends each definition that it copies out with the comment:

/* ### DEFNCOPY: END OF DEFINITION */

Definitions created as text must end with this comment so thatdefncopy can copy them in successfully.

• Enclose values specified to defncopy in quotation marks, if theycontain characters that could be significant to the shell.

◆ WARNING!Long comments (more than 100 characters) placed before a createstatement may cause defncopy to fail.



Examples

1. defncopy -Usa -P -SMERCURY in new_proc stagedb

Copies definitions from the file new_proc into the database stagedb onserver MERCURY. The connection with MERCURY is establishedwith a user of name “sa” and a NULL password.

2. defncopy -S -z french out dc.out employeessp_calccomp sp_vacation

Copies definitions for objects “sp_calccomp” and “sp_vacation”from the “employees” database on the SYBASE server to the filedc.out. Messages and prompts are displayed in “french”. Theuser is prompted for a password.


dscp Adaptive Server Enterprise Release 11.5.x

dscp

Function

Allows you to view and edit server entries in the interfaces file fromthe command line.

Syntax

dscp [-p]

or

dscp -v

To exit from dscp: quit or exit

Parameters

-p – suppresses command-line prompts.

-v – displays the version and copyright message of dscp and returns tothe operating system.

Comments

• The dscp utility program is a text-based utility.

• For more information about the dscp utility program, seeConfiguring Adaptive Server for UNIX Platforms.

Examples

1. dscp -p

Opens the default interfaces file for editing and suppresses thecommand-line prompt.

See Also

Utilities dsedit


Adaptive Server Enterprise Release 11.5.x dsedit

dsedit

Function

Allows you to view and edit server entries in the interfaces file using agraphical user interface based on X11/Motif.

Syntax

dsedit

or

dsedit -v

Parameters

-v – displays the version and copyright message of dsedit.

Comments

• You must set the SYBASE environment variable to the location ofthe current version of Adaptive Server before you can use dsedit.

• You must set the DISPLAY environment variable before invokingdsedit, unless you are only using the -v parameter to display theversion number.

• For more information about the dsedit utility program, seeConfiguring Adaptive Server for UNIX Platforms.

See Also

Utilities dscp


isql Adaptive Server Enterprise Release 11.5.x

isql

Function

Interactive SQL parser to Adaptive Server.

Syntax

isql [-b] [-e] [-F] [-n] [-p] [-X] [-Y][-a display_charset ][-A size ][-c cmdend][-D database ][-E editor ][-h headers ][-H hostname ][-i inputfile ][-I interfaces_file ][-J client_charset ][-l login_timeout ][-m errorlevel ][-o outputfile ][-P password ][-R remote_server_principal ][-s colseparator ][-S server ][-t timeout ][-U username ][-w columnwidth ][-z language ]

or

isql -v

To terminate a command: go

To clear the query buffer: reset

To call the default editor: vi

To execute an operating system command: !! command

To exit from isql: quit or exit

Parameters

-b – disables the display of the table headers output.

-e – echoes input.


Adaptive Server Enterprise Release 11.5.x isql

-F – enables the FIPS flagger. When you specify the -F parameter, theserver flags any non- standard SQL commands.

-n – removes numbering and the prompt symbol (>) from the echoedinput lines in the output file when used in conjunction with -e.

-p – prints performance statistics.

-X – initiates the login connection to the server with client-sidepassword encryption. isql (the client) specifies to the server thatpassword encryption is desired. The server sends back anencryption key, which isql uses to encrypt your password, and theserver uses the key to authenticate your password when itarrives.

If isql crashes, the system creates a core file that contains yourpassword. If you did not use the encryption option, thepassword appears in plain text in the file. If you used theencryption option, your password is not readable.

-Y – tells the Adaptive Server to use chained transactions.

-a display_charset – runs isql from a terminal whose character setdiffers from that of the machine on which isql is running. Use -a inconjunction with -J to specify the character set translation file (.xltfile) required for the conversion. Use -a without -J only if the clientcharacter set is the same as the default character set.



any 7-bit ASCII character can pass unaltered between client and server.

Other characters produce conversion errors. See Chapter 14, “Configuring

Client/Server Character Set Conversions” in the System AdministrationGuide for more information on character set conversion.

-A size – specifies the network packet size to use for this isql sessionFor example:

isql -A 2048

sets the packet size to 2048 bytes for this isql session. To checkyour network packet size, enter:

select * from sysprocesses

The value is displayed under the network_pktsz heading.



size must be between the values of the default network packet size andmaximum network packet size configuration parameters, and must bea multiple of 512.

Use larger-than-default packet sizes to perform I/O-intensiveoperations, such as readtext or writetext operations.

Setting or changing Adaptive Server’s packet size does not affectthe packet size of remote procedure calls.

-c cmdend – changes the command terminator. By default, youterminate commands and send them to Adaptive Server bytyping “go” on a line by itself. When you change the commandterminator, do not use SQL reserved words or control characters.

-D database – selects the database in which the isql session begins.

-E editor – specifies an editor other than the default editor vi.

-h headers – specifies the number of rows to print between columnheadings. The default prints headings only once for each set ofquery results.

-H hostname – sets the client hostname.

-i inputfile – specifies the name of the operating system file to use forinput to isql. The file must contain command terminators (“go” isthe default).

Specifying the parameter as follows:

-i inputfile

is equivalent to:

< inputfile

If you use -i and do not specify your password on the commandline, isql prompts you for it.

If you use < inputfile and do not specify your password on thecommand line, you must specify your password as the first lineof the input file.

-I interfaces_file – specifies the name and location of the interfaces fileto search when connecting to Adaptive Server. If you do notspecify -I, isql looks for a file named interfaces in the directoryspecified by your SYBASE environment variable.

-J client_charset – specifies the character set to use on the client.-J client_charset requests that Adaptive Server convert to and from



client_charset, the character set used on the client. A filter convertsinput between client_charset and the Adaptive Server characterset.

-J with no argument sets character set conversion to NULL. Noconversion takes place. Use this if the client and server use thesame character set.

Omitting -J sets the character set to a default for the platform.The default may not necessarily be the character set that theclient is using. See Chapter 14, “Configuring Client/ServerCharacter Set Conversions,” in the System Administration Guidefor more information about character sets and the associatedflags. Table 1-2 lists platform defaults.

-l login_timeout – specifies the maximum timeout value allowed whenconnecting to Adaptive Server. The default is 60 seconds. Thisvalue affects only the time that isql waits for the server to respondto a login attempt. To specify a timeout period for commandprocessing, use the -t timeout parameter.

-m errorlevel – customizes the error message display. For errors of theseverity level specified or higher, only the message number, state,and error level are displayed; no error text appears. For errorlevels lower than the specified level, nothing appears.

-o outputfile – specifies the name of an operating system file to storethe output from isql. Specifying the parameter as follows:

-o outputfile

is similar to:

> outputfile

Table 1-6: Default character sets for different platforms

Platform Default Character Set

Sun Solaris, Digital UNIX, Pyramid, NCR,RS/6000

iso_1

HP-UX roman8

OS/2, Novell NetWare 386 cp850

Macintosh mac



-P password – specifies your Adaptive Server password. If you do notspecify the -P flag, isql prompts for a password. If your passwordis NULL, use the -P flag without any password.

-R remote_server_principal – specifies the principal name for the serveras defined to the security mechanism. By default, a server’sprincipal name matches the server’s network name (which isspecified with the -S parameter or the DSQUERY environmentvariable). Use the -R parameter when the server’s principal nameand network name are not the same.

-s colseparator – resets the column separator character, which is blankby default. To use characters that have special meaning to theoperating system (for example, “|”, “;”, “&”, “<”, “>”), enclosethem in quotes or precede them with a backslash.

-S server – specifies the name of the Adaptive Server to which toconnect. isql looks this name up in the interfaces file. If you specify-S with no argument, isql looks for a server named SYBASE. If youdo not specify -S, isql looks for the server specified by yourDSQUERY environment variable.

-t timeout – specifies the number of seconds before a SQL commandtimes out. If you do not specify a timeout, the command runsindefinitely. This affects commands issued from within isql, notthe connection time. The default timeout for logging into isql is 60seconds.

-U username – specifies a login name. Login names are case sensitive.

-w columnwidth – sets the screen width for output. The default is 80characters. When an output line reaches its maximum screenwidth, it breaks into multiple lines.

-z language – is the official name of an alternate language to displayisql prompts and messages. Without -z, isql uses the server’sdefault language. You can add languages to an Adaptive Serverduring installation or afterward, using the langinstall utility or thesp_addlanguage stored procedure.

-v – prints the version number and copyright message for isql andthen exits.



Examples

1. isql -Ujoe -Pabracadabra

1> select *2> from authors3> where city = "Oakland"4> vi

Puts you in a text file where you can edit the query. When youwrite and save the file, you are returned to isql. The queryappears; type "go" on a line by itself to execute it.

2. isql -UalmaPassword:

1> select *2> from authors3> where city = "Oakland"4> reset1> quit

reset clears the query buffer. quit returns you to the operatingsystem.

3. isql -a mac -J roman8

Specifies that you are running isql from a Macintosh against aserver that is using the roman8 character set.

Comments

• The 5701 (“changed database”) server message is no longerdisplayed after login or issuing a use database command.

• There are two new optional flags:

-b – disables column headers from printing-D database – selects the startup database that isql uses

• Error message format differs from previous versions of isql. If youhave scripts that perform routines based on the values of thesemessages you may need to re-write them.

• To use isql interactively, give the command isql (and any of theoptional parameters) at your operating system prompt. The isqlprogram accepts SQL commands and sends them to AdaptiveServer. The results are formatted and printed on standard output.Exit isql with quit or exit.

• Send a command to Adaptive Server by typing the commandterminator (go, by default) at the beginning of a line. You can



follow the command terminator with an integer to specify howmany times to run the command. For example, to execute acommand 100 times, type:

select x = 1go 100

The results display once at the end of execution.

• The -y sybase_directory parameter has been removed.

• If you enter an option more than once on the command line, isqluses the last value. For example, if you enter the followingcommand:

isql -c"." -csend

“send”, the second value for -c, overrides “.”, the first value. Thisenables you to override any aliases you set up.

• To call an editor on the current query buffer, enter its name as thefirst word on a line. Define your preferred callable editor byspecifying it with the EDITOR environment variable. If EDITORis not defined, the default is vi.

Execute operating system commands by starting a line with “!!”followed by the command. Call alternate editors this way,without defining EDITOR.

• To clear the existing query buffer, type reset on a line by itself. isqldiscards any pending input. You can also press Ctrl-c anywhereon a line to cancel the current query and return to the isql prompt.

• Read in an operating system file containing a query for executionby isql as follows:

isql -U alma -P passwd < input_file

The file must include a command terminator. The results appearon your terminal. Read in an operating system file containing aquery and direct the results to another file as follows:

isql -U alma -P passwd < input_file > output_file

• Case is significant for the isql flags.

• isql displays only 6 digits of float or real data after the decimalpoint, rounding off the remainder.

• When you are using isql interactively, read an operating systemfile into the command buffer with the command:

:r filename



Do not include the command terminator in the file; once youhave finished editing, enter the terminator interactively on a lineby itself.

• You can include comments in a Transact-SQL statementsubmitted to Adaptive Server by isql. Open a comment with “/*”.Close it with “*/”, as shown in the following example:

select au_lname, au_fname/*retrieve authors’ last and first names*/from authors, titles, titleauthorwhere authors.au_id = titleauthor.au_idand titles.title_id = titleauthor.title_id/*this is a three-way join that links authors**to the books they have written.*/

If you want to comment out a go command, it should not be atthe beginning of a line. For example:

/*

**go

*/

should be used to comment out the go command instead of:

/*

go

*/

See Also

System procedures sp_addlanguage, sp_addlogin, sp_configure,sp_defaultlanguage, sp_droplanguage,sp_helplanguage


langinstall Adaptive Server Enterprise Release 11.5.x

langinstall

Function

Installs a new language in an Adaptive Server.

Syntax

langinstall[-S server ][-I interfaces_file ][-P password ][-R release_number ]language character_set

or

langinstall -v

Parameters

-S server – specifies the name of the Adaptive Server to which toconnect. If you do not specify -S, langinstall uses the serverspecified by your DSQUERY environment variable. If DSQUERYis not set, langinstall attempts to connect to a server namedSYBASE.

-I interfaces_file – specifies the name and location of the interfaces filethat langinstall searches when connecting to Adaptive Server. Ifyou do not specify -I, langinstall uses the interfaces file in thedirectory specified by the SYBASE environment variable. IfSYBASE is not set, langinstall looks for the default SYBASEdirectory.

-P password – specifies the “sa” account password. If you omit -P,langinstall prompts for the “sa” account password.

-R release_number – specifies the release number, in the format n.n.n.,to use to upgrade messages in master..sysmessages. Use -R only infailure conditions, such as if langinstall fails or in case of user erroror when you think that messages in sysmessages are out of date.

language – is the official name of the language to be installed. Youmust specify a language.

character_set – is the name of Adaptive Server’s default character set.character_set indicates the directory name of the localization filesfor the language. The common.loc and server.loc localization files


Adaptive Server Enterprise Release 11.5.x langinstall

for an official language reside in the character set directory$SYBASE/locales/language/character_set. You must specify acharacter set.

-v – prints the version number and copyright message for langinstalland then exits.

Comments

• The Adaptive Server installation program runs langinstallautomatically for a new installation as well as for customers whoare upgrading from a previous release.

• langinstall does the following:

- Adds the specified language-specific information tomaster..syslanguages using the sp_addlanguage stored procedure.If the language already exists, langinstall updates theappropriate row in syslanguages.

- Adds to, updates, and deletes error messages as necessaryfrom master..sysmessages.

- Updates syslanguages.update, inserting the new release number.

• langinstall validates the entries in the localization file sections thatit uses. If anything is missing, langinstall prints an error messageand does not add the language to syslanguages.

• langinstall compares the version numbers of each localization file ituses, common.loc and server.loc. If they are not the same, it prints awarning message. syslanguages.upgrade is always set according tothe version number in server.loc.

• The -R parameter forces langinstall to collect messages from arelease previous to the current one. langinstall compares theexisting messages with the ones to be installed and replaces anythat have changed.

For example, if the current release is 11.5, and the previousrelease was 10.0, and you think sysmessages may not be correct,include the messages from the previous release in thesyslanguages.upgrade column (10.0 in this case) by specifying -R10.0. langinstall then installs all messages from Adaptive Server10.0.

Permissions

Only the “sa” account can run langinstall.


langinstall Adaptive Server Enterprise Release 11.5.x

Tables Used

master.dbo.syslanguages, master.dbo.sysmessages

See Also

System procedures sp_addlanguage, sp_addlogin, sp_configure,sp_defaultlanguage, sp_droplanguage,sp_helplanguage

Utilities srvbuild


Adaptive Server Enterprise Release 11.5.x showserver

showserver

Function

Shows the Adaptive Servers and Backup Servers that are currentlyrunning on the local machine.

Syntax

showserver

Parameters

None.

Examples

1. showserver

USER PID %CPU %MEM SZ RSS TT STAT START TIME COMMAND

user114276 0.0 1.7 712 1000 ? S Apr 5514:05 dataserver-d greensrv.dat -sgreensrv -einstall/greensrv+_errorlog

sybase 1071 0.0 1.4 408 820 ? S Mar 28895:38/usr/local/sybase/bin/dataserver -d/dev/rsd1f-e/install/errorlog

user128493 0.0 0.0 3692 0 ? IW Apr 1 0:10 backupserver-SSYB_BACKUP -e/install/backup.log -Iinterfaces -Mbin/sybmultbuf-Lus_english -Jiso_1

Comments

• showserver displays process information about Adaptive Server orBackup Server. If no servers are running, only the headerappears.

See Also

Utilities langinstall

Commands dataserver, startserver

Functions host_name


sqlloc Adaptive Server Enterprise Release 11.5.x

sqlloc

Function

Installs and modifies languages, character sets, and sort orderdefaults for Adaptive Server using a graphical user interface basedon X11/Motif.

Syntax

sqlloc[-S server ][-U user ][-P password ][-s sybase_dir ][-I interfaces_file ][-r resource_file ]

or

sqlloc -v

Parameters

-S server – specifies the name of the Adaptive Server to which toconnect.

-U user – specifies a login name. Logins are case sensitive.

-P password – specifies the “sa” account password.

-s sybase_dir – specifies the value to use for the SYBASE environmentvariable.

-I interfaces_file – specifies the name and location of the interfaces fileto search when connecting to Adaptive Server.

-r resource_file – executes the specified resource file.

-v – prints the version number and copyright message for sqlloc andthen exits.

Comments

• You must set the SYBASE environment variable to the location ofthe current version of Adaptive Server before you can use sqlloc.

• You must set the DISPLAY environment variable before invokingsqlloc, unless you are only using the -v parameter to display theversion number.


Adaptive Server Enterprise Release 11.5.x sqlloc

• For more information about the sqlloc utility program, seeInstalling Adaptive Server and OmniConnect on UNIX Platforms orConfiguring Adaptive Server for UNIX Platforms.

Permissions

You must be a Sybase System Administrator to use sqlloc.

See Also

Utilities langinstall, sqllocres


sqllocres Adaptive Server Enterprise Release 11.5.x

sqllocres

Function

Installs and modifies languages, character sets, and sort orderdefaults for Adaptive Server, using a resource file.

Syntax

sqllocres[-S server ][-U user ][-P password ][-s sybase_dir ][-I interfaces_file ][-r resource_file ]

or

sqllocres -v

Parameters

-S server – specifies the name of the Adaptive Server to which toconnect.

-U user – specifies a login name. Logins are case sensitive.

-P password – specifies the “sa” account password.




-v – prints the version number and copyright message for sqllocresand then exits.

Comments

• You must set the SYBASE environment variable to the location ofthe current version of Adaptive Server before you can usesqllocres.

• For more information about the sqllocres utility program, seeInstalling Adaptive Server and OmniConnect on UNIX Platforms.


Adaptive Server Enterprise Release 11.5.x sqllocres

Permissions

You must be a Sybase System Administrator to use the sqllocresutility.

See Also

Utilities langinstall,sqlloc


sqlupgrade Adaptive Server Enterprise Release 11.5.x

sqlupgrade

Function

Upgrades your currently installed release of Adaptive Server to thenewest release using a graphical user interface based on X11/Motif.

Syntax

sqlupgrade[-s sybase_dir ][-r resource_file ]

or

sqlupgrade -v

Parameters



-v – prints the version number and copyright message for sqlupgradeand then exits.

Comments

• You must set the SYBASE environment variable to the location ofthe current version of Adaptive Server before you can usesqlupgrade.

• You must set the DISPLAY environment variable before invokingsqlupgrade, unless you are only using the -v parameter to displaythe version number.

• For more information about the sqlupgrade utility program, seeInstalling Adaptive Server and OmniConnect on UNIX Platforms.

Permissions

You must be a Sybase System Administrator to use sqlupgrade.

See Also

Utilities sqlupgraderes


Adaptive Server Enterprise Release 11.5.x sqlupgraderes

sqlupgraderes

Function

Upgrades your currently installed release of Adaptive Server to thenewest release using resource files.

Syntax

sqlupgraderes[-s sybase_dir ][-r resource_file ]

or

sqlupgraderes -v

Parameters



-v – prints the version number and copyright message forsqlupgraderes and then exits.

Comments

• You must set the SYBASE environment variable to the location ofthe current version of Adaptive Server before you can usesqlupgraderes.

• For more information about the sqlupgraderes utility program, seeInstalling Adaptive Server and OmniConnect on UNIX Platforms.

Permissions

You must be a Sybase System Administrator to use the sqlupgraderesutility.

See Also

Utilities sqlupgrade


srvbuild Adaptive Server Enterprise Release 11.5.x

srvbuild

Function

Creates a new Adaptive Server, Backup Server, Monitor Server, or XPServer with default or user-specified values for key configurationattributes using a graphical user interface based on X11/Motif.

Syntax

srvbuild[-s sybase_dir ][-I interfaces_file ][-r resource_file ]

or

srvbuild -v

Parameters




-v – prints the version number and copyright message for srvbuild andthen exits.

Comments

• You must set the SYBASE environment variable to the location ofthe current version of Adaptive Server before you can use srvbuild.

• You must set the DISPLAY environment variable before invokingsrvbuild, unless you are only using the -v parameter to display theversion number.

• For more information about the srvbuild utility program, seeInstalling Adaptive Server and OmniConnect on UNIX Platforms.

Permissions

You must be a Sybase System Administrator to use the srvbuild utility.


Adaptive Server Enterprise Release 11.5.x srvbuild

See Also

Utilities srvbuildres


srvbuildres Adaptive Server Enterprise Release 11.5.x

srvbuildres

Function

Creates, using resource files, a new Adaptive Server, Backup Server,Monitor Server, or XP Server with default or user-specified values forkey configuration attributes.

Syntax

srvbuildres[-s sybase_dir ][-I interfaces_file ][-r resource_file ]

or

srvbuildres -v

Parameters




-v – prints the version number and copyright message for srvbuildresand then exits.

Comments

• You must set the SYBASE environment variable to the location ofthe current version of Adaptive Server before you can usesrvbuildres.

• For more information about the srvbuildres utility program, seeInstalling Adaptive Server and OmniConnect on UNIX Platforms.

Permissions

You must be a Sybase System Administrator to use the srvbuildresutility.


Adaptive Server Enterprise Release 11.5.x srvbuildres

See Also

Utilities srvbuild


startserver Adaptive Server Enterprise Release 11.5.x

startserver

Function

Starts an Adaptive Server and/or a Backup Server.

Syntax

startserver [[-f runserverfile ] [-m]] ...

Parameters

-f runserverfile – specifies the relative path name of a runserver file,which is used as a reference each time you start an AdaptiveServer or Backup Server. By default, the runserver file is in thecurrent directory and is named RUN_servername. If you start asecond Adaptive Server on the same machine, startserver creates anew runserver file named RUN_servername.

-m – starts Adaptive Server in single-user mode, allowing only oneSystem Administrator to log in, and turns the allow updates to systemtables configuration parameter on. Use this mode to restore themaster database. The System Administrator can use the dbo use onlyparameter of sp_dboption for system administration activities thatrequire more than one process, such as bulk copying or using thedata dictionary. startserver normally starts up only one server pernode.

The -m parameter creates an m_RUNSERVER file and overwritesany existing m_RUNSERVER file.

Examples

1. startserver

Starts an Adaptive Server named SYBASE from the runserverfile named RUN_servername in the current directory.

2. startserver -f RUN_MYSERVER -f RUN_SYB_BACKUP

Starts an Adaptive Server named MYSERVER and a BackupServer named SYB_BACKUP.

3. startserver -f RUN_SYB_BACKUP

Starts only the Backup Server SYB_BACKUP.


Adaptive Server Enterprise Release 11.5.x startserver

Comments

• startserver uses the information in the runserver file to start anAdaptive Server or Backup Server. The master device must bewritable by the user who starts Adaptive Server.

The startserver command creates the Adaptive Server error log file(named errorlog) in the directory where the server is started, andadds this information as part of the -e parameter in the AdaptiveServer executable line in the runserver file. If a second AdaptiveServer is started on the same machine, a new error log namederrorlog_servername is created; this information is added to thatserver’s runserver file. The user must have execute permissionon the specified runserver file.

• You can start multiple servers by specifying more than onerunserver file, as shown in example 2. You can specify -m aftereach -f runserverfile.

• Adaptive Server derives its running environment from values inthe config file. Run the system procedure sp_configure or edit theconfig file to see or change configuration parameters.

• To ensure the integrity of your Adaptive Server, it is importantthat you apply appropriate operating system protections to thestartserver executable and the runserver file.

The Runserver File

• The runserver file, which is created by srvbuild during installation,contains the dataserver command to start Adaptive Server or thebackupserver command to start Backup Server. By default, therunserver file is in the current directory and is namedRUN_servername. You can edit the runserver file to correct theoptions and parameters for the commands. The followingexample shows two sample runserver files:


startserver Adaptive Server Enterprise Release 11.5.x

Runserver file for server MYSERVER:

#!/bin/sh## Adaptive Server Information:# name: /MYSERVER# master device: /remote/Masters/myserver_dat# master device size: 10752# errorlog: /remote/serverdev/install/errorlog# interfaces: /remote/serverdev/interfaces##/remote/serverdev/bin/dataserver -d/remote/Masters/myserver_dat \-sMYSERVER -e/remote/serverdev/install/MYSERVER_errorlog \-i/remote/serverdev &

Runserver file for backup server SYB_BACKUP:

#!/bin/sh## Backup Server Information:# name: SYB_BACKUP# errorlog: /remote/serverdev/install/backup.log# interfaces: /remote/serverdev/interfaces# location of multibuf: /remote/serverdev/bin/sybmultbuf# language: us_english# character set: iso_1# tape configuration file: /remote/serverdev/backup_tape.cfg##/remote/serverdev/bin/backupserver -SSYB_BACKUP \-e/remote/serverdev/install/backup.log \-I/remote/serverdev/interfaces \-M/remote/serverdev/bin/sybmultbuf -Lus_english -Jiso_1 \-c/remote/serverdev/backup_tape.cfg

See Also

Utilities backupserver, dataserver


Adaptive Server Enterprise Release 11.5.x sybload

sybload

Function

Uploads Sybase products from the distribution media and builds theSybase installation directory.

Syntax

sybload [-D]

Parameters

-D – uploads Adaptive Server from the distribution media.

Comments

• sybload is a command-line utility.

• You must set the SYBASE environment variable to the location ofthe current version of Adaptive Server before you can use sybload.

• sybload issues rsh commands to the UNIX shell to unload productsremotely. Be sure the default version of the program on the hostsystem is /bin/rsh, or sybload will fail.

• For more information about installing Adaptive Server, see theSybase installation documentation for your platform and theadministration documentation for your operating system.

• You cannot perform a remote installation from CD-ROM usingsybload. For information on how to install remotely from a CD-ROM, see the installation documentation for your platform.

• sybload prompts you for installation information. Table 1-7summarizes the information you need to enter.

Table 1-7: Summary of sybload prompts for tape installation

Prompt Value

Sybase directory Confirm that the current directory is the Sybase installationdirectory, or specify the correct directory path.

Local or remoteinstallation

Enter either:

“L” – local“R” – remote installation

Name of the non-rewinding tape drive

Enter the name of the device from which you are loading yourSYBASE software—must be the non-rewinding name.


sybload Adaptive Server Enterprise Release 11.5.x

• For more information about sybload, see Installing Adaptive Serverand OmniConnect on UNIX Platforms.

Customer AuthorizationString (CAS)

Enter the string from the software packaging that allows youto access your products.

Sybase products Select products to install from the sybload menu:

-Enter the number of each product you plan to install-Press Return after each number-Press Return twice to enter a blank line when finished

Product confirmation The sybload utility lists the products you have chosen. Enter:

“y” – correct“q” – quitAny_other_character to display the menu and choose again

Table 1-7: Summary of sybload prompts for tape installation (continued)

Prompt Value


Adaptive Server Enterprise Release 11.5.x sybsetup

sybsetup

Function

Installs and configures Adaptive Server from a single location using agraphical user interface based on X11/Motif.

Syntax

sybsetup[-c devicename ][-t devicename ][-l local_source ][-r remote_source ]

Parameters

-c devicename – is the CD-ROM directory path of your AdaptiveServer distribution media.

-t devicename – is the tape device directory path of your AdaptiveServer distribution media.

-l local_source – is the path name of the local tape device.

-r remote_source – is the path name of the remote tape device.

Comments

• You must set the SYBASE environment variable to the location ofthe current version of Adaptive Server before you can usesybsetup.

• You must set the DISPLAY environment variable before usingsybsetup.

• For more information about the sybsetup utility program, seeInstalling Adaptive Server and OmniConnect on UNIX Platforms.

Permissions

You must be the Sybase System Administrator to use the sybsetuputility.


sybsetup Adaptive Server Enterprise Release 11.5.x


2 Using bcp to Transfer Data to andfrom Adaptive Server 2.

This chapter explains how to use the bulk copy utility, bcp, to movedata between Adaptive Server and an operating system file.

Topics covered are:

• Methods of Moving Data 2-1

• Requirements for Using bcp 2-2

• bcp Performance Issues 2-3

• Using the bcp Options 2-13

• Changing the Defaults: Interactive bcp 2-15

• Using Format Files 2-20

• Examples: Copying Out Data Interactively 2-23

• Examples: Copying In Data Interactively 2-27

• Using bcp with Alternate Languages 2-30

• Copy In and Batches 2-31

• Specifying a Network Packet Size 2-32

• Copy Out and text and image Data 2-32

• Specifying a Network Packet Size 2-32

• Copy In and Error Files 2-32

• Copy Out and Error Files 2-33

• Data Integrity: Defaults, Rules, and Triggers 2-33

• How bcp Differs from Other Utilities 2-34

See “bcp” on page 1-7 for the full command syntax and descriptionsof the parameters.

Methods of Moving Data

Three methods are available for moving data:

• Using bcp as a standalone program from the operating system.This method is described in this chapter.

2-2 Using bcp to Transfer Data to and from Adaptive Server

Requirements for Using bcp Adaptive Server Enterprise Release 11.5.x

• Using Client-Library, which calls bulk library routines. See theOpen Client and Open Server Common Libraries Reference Manual fordetails.

• Using Client-Library™ applications, which can call Client-Library routines. See the Open Client Client-Library/C ReferenceManual for details.

Using bcp to Import and Export Data

No Transact-SQL commands are used for the bulk transfer of data.Instead, you use bcp from the operating system command line.

bcp is most frequently used to import data that was previouslyassociated with another program (such as another databasemanagement system). Use the dump facilities from the otherprogram to put the data to be transferred into an operating systemfile.

You can also use bcp to move tables between Adaptive Servers orbetween Adaptive Server and other data sources that can produce anoperating system file.

You can use bcp to copy data out of a view. However, you cannot usebcp to copy data into a view. See “bcp” on page 1-6 for a descriptionof the syntax for using bcp to copy out from a view.

bcp can also transfer data for use with other programs, for example,with spreadsheet programs. bcp moves the data from AdaptiveServer into an operating system file; from there the other programimports the data. When you finish using your data with the otherprogram, transfer it back into the operating system file, and then usebcp to copy it back into Adaptive Server.

Adaptive Server can accept data in any character or binary format, aslong as the terminators (the characters used to separate columns) orthe length of the fields are described in the data file. The tablestructures need not be identical. When importing from a file, bcpappends data to an existing database table; when exporting to a file,bcp overwrites the previous contents of the file.

Requirements for Using bcp

You must supply the following information for transferring data toand from Adaptive Server:

• Name of the database and table or view


Adaptive Server Enterprise Release 11.5.x bcp Performance Issues

• Name of the operating system file

• Direction of the transfer (in or out)

In addition, you can (optionally) modify the storage type, storagelength, and terminator for each column.

When the transfer is complete, bcp reports the number of rowssuccessfully copied and some performance information.

Permissions Needed for Copying Data

To use bcp, you must have an Adaptive Server account and theappropriate permissions on the database tables or views andoperating system files that you will use.

To copy data into a table, you must have insert permission on thetable.

To copy a table to an operating system file, you must have selectpermission on the following tables:

• The table being copied

• sysobjects

• syscolumns

• sysindexes

bcp Performance Issues

bcp in works in one of two modes: fast or slow. The differencesbetween the two methods are described in Table 2-1:

Depending on the size of the table into which you are copying data,the amount of data you are copying in, the number of indexes on thetable, and the amount of spare database device space that you have

Table 2-1: Characteristics of fast and slow bcp

Typeof bcp

Characteristics

Slow Logs each row insert it makes in a table. Used for tables thathave one or more indexes or triggers.

Fast Logs only page allocations. Copies data into tables withoutindexes or triggers at the fastest speed possible.


bcp Performance Issues Adaptive Server Enterprise Release 11.5.x

for re-creating indexes, you can decide whether to use fast or slowbcp. Although fast bcp might enhance performance, slow bcp givesyou greater recoverability.

Before you can use bcp, you must set select into/bulkcopy/pllsort to true.For example to turn on this option for the pubs2 database, enter:

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

Remove indexes and triggers on the target table to use fast bcp.

Keeping indexes and triggers on the table causes the bulk copyutility to use slow bcp; however, slow bcp causes the transaction log tofill very quickly. If you are copying a large number of rows, theperformance penalty and log space requirements for using slow bcpcan be severe. For extremely large tables, using slow bcp is not anoption—it is much too slow.

The performance of bcp can be improved even further withpartitioned tables. By using several bcp sessions with a partitionedtable, you can dramatically reduce the time required to copy thedata. Such performance improvements are more noticeable in fastbcp than slow bcp.

Using bcp in parallel mode can dramatically increase performance.See “Using Parallel Bulk Copy to Copy Data Into a Specific Partition”on page 2-9 for information about using parallel bulk copy. Parallelbulk copy can be used to provide balanced data distribution acrosspartitions.

The following sections discuss these issues in detail.

Using Fast or Slow bcp

For copying data in, bcp is fastest if your database table has noindexes or triggers, because fast bcp does not log data inserts in thetransaction log. Fast bcp logs only the page allocations.

When you copy into a table that has indexes or triggers, bcpautomatically uses a slower version, which logs data inserts in thetransaction log. This can cause the transaction log to become verylarge, but dumping the log to a backup device with dump transactionassures that the database is fully recoverable in the event of a failure.

If you have made unlogged data inserts with fast bcp, you cannotdump the transaction log to a device, because changes are not in thelog and, therefore, are not recoverable from such a dump. In thissituation, issuing dump transaction to a device produces an error



message instructing you to use dump database instead. This restrictionremains in force until a dump database successfully completes.

bcp does not fire the triggers, if any exist, on the target table.

➤ NoteTo allow a user to copy in data using the fast version of bcp, a System

Administrator or the Database Owner must first use the sp_dboption system

procedure to set select into/bulkcopy/pllsort to true for the database containing

the target table(s). If the option is set to false, and a user tries to copy data

into a table that does not have indexes or triggers, Adaptive Server

generates an error message.

You do not need to set select into/bulkcopy/pllsort true to copy data out ofor to copy data into a table that has indexes or triggers. Tables withindexes or triggers are always copied with slow bcp, and all insertsare logged.

The following table shows which version of bcp is used whencopying in, the necessary settings for the select into/bulkcopy/pllsortoption, and whether the transaction log can be dumped.

By default, the select into/bulkcopy/pllsort option is set to false in newlycreated databases. To change the default setting, turn this option on(set to true) in the model database.

If you are copying a very large number of rows, drop all the indexesand triggers beforehand with drop index and drop trigger, set selectinto/bulkcopy/pllsort to true, copy the data into the table, re-create theindexes and triggers, and then dump the database to increase thespeed of copying. Remember to allocate 1.2 times the amount of

Table 2-2: Fast and slow bcp with select into/bulkcopy/pllsort

select into/bulkcopy/pllsort on

select into/bulkcopy/pllsort off

fast bcp

(no indexes or triggers ontarget table)

OK

dump transactionto a deviceprohibited

bcpprohibited

slow bcp

(one or more indexes ortriggers)

OK

dump transactionOK

OK

dump transactionOK



space needed for the data, plus enough space to reconstruct aclustered index. If you don’t have enough space for the server to sortthe data and build the index(es), use slow bcp.

Steps for Copying Data

Table 2-3 summarizes the steps for copying data into AdaptiveServer using fast bcp.

Bulk Copying Data into Partitioned Tables

In certain circumstances, you can improve bcp performancedramatically by executing several bcp sessions with a partitionedtable. Partitioned tables improve insert performance by reducinglock contention and by distributing I/O over multiple devices. bcpperformance with partitioned tables is improved primarily becauseof distributed I/O.

Guidelines

When you execute a bcp session on a partitioned table, consider thefollowing:

Table 2-3: Steps for copying data using fast bcp

Step Who Can Do It

Use sp_dboption to set select into/bulkcopy/pllsort to true,and then run checkpoint in the database that waschanged.

System Administrator or Database Owner

Drop the indexes and triggers on the table.

(Make sure you have enough space to re-create them).

Table owner

Be sure that you have insert permission on the table. Granted by the table owner

Perform the copy with bcp. Any user with insert permission

Re-create the indexes and triggers. Table owner

Reset sp_dboption, if desired, and run checkpoint in thedatabase that was changed.

System Administrator or Database Owner

Use dump database to back up the newly inserted data. System Administrator, Operator, orDatabase Owner

Run stored procedures or queries to see if any of thenewly loaded data violates rules.

Table owner or stored procedure owner



• A partitioned table improves performance only when you arebulk copying into the table.

• The performance of slow bcp does not improve as much withpartitioned tables. Drop all indexes and triggers and use fast bcp,as described in Table 2-3 on page 2-6.

• If possible, use a local connection to the Adaptive Server.Network traffic can quickly become a bottleneck when multiplebcp sessions are being executed.

Methods of Copying Data into a Partition

There are two methods for copying data into a partitioned heaptable:

• Copying the data randomly without regard to the partition towhich data is copied.

• Copying the data into a specific partition.

If the table has a clustered index, slow bcp is used, and the indexcontrols the placement of rows.

These methods are outlined below.

Copying Data into Partitions Randomly

For a detailed description of copying data into partitioned tables, seeChapter 17, “Controlling Physical Data Placement,” in thePerformance and Tuning Guide.

Following is an overview of the actions you must perform whenusing multiple bcp sessions to copy data randomly into partitionedtables:

1. Configure the table with as many partitions and physical devicesas you require for your system. See Chapter 7, “Improving InsertPerformance with Partitions” in the Performance and Tuning Guideand “Using Parallel Bulk Copy to Copy Data Into a SpecificPartition” in this manual for information.

2. Make sure Adaptive Server is configured with enough locks tosupport multiple bcp sessions. See Chapter 11, “SettingConfiguration Parameters” in the System Administration Guidefor information on configuring locks.

3. Follow the instructions in “Using Fast or Slow bcp” on page 2-4to remove the triggers and indexes on the table and enable fastbcp. If you use slow bcp, performance may not improve



significantly. Also, if the table contains indexes, you mayexperience deadlocks on the index pages.

4. Divide the bcp input file into as many files of equal size as thenumber of planned simultaneous bcp sessions, or use the (-F)firstrow and (-L) lastrow options to specify the start and end ofeach input file.

5. Execute the bcp sessions with separate files in parallel, preferablyon the local Adaptive Server machine. For example, on UNIXplatforms, you can execute different sessions in different shellwindows or start individual bcp sessions in the background.

Monitor the bcp Session with dbcc checktable and sp_helpsegment

If you do not specify which partition bcp sessions should use,Adaptive Server randomly assigns the multiple bcp sessions to thetable’s available partitions. If so, monitor the partitions to determinewhether the inserts were distributed evenly. Use the dbcc checktablecommand periodically to check the total page counts for eachpartition. You can also use sp_helpsegment or sp_helppartition, which donot lock the database objects, to perform a similar check. See Chapter18, “Checking Database Consistency,” in the System AdministrationGuide for more information about dbcc checktable. See the AdaptiveServer Reference Manual for more information about sp_helpsegmentand sp_helppartition.

For more information about table partitions, see “Improving InsertPerformance with Partitions” on page 17-14 in the Performance andTuning Guide.

Reducing Logging by Increasing Page Allocations

If you are using fast bcp, consider that each bcp in batch requires thepage manager to allocate one or more extents, which generates asingle log record. The number of preallocated extents configurationparameter specifies how many extents are allocated by AdaptiveServer by the page manager. Valid values for the number of preallocatedextents configuration parameter are from 1 to 31. You must rebootAdaptive Server to change the value. When you are performing largebcp operations, increase this number to prevent the page allocationsfrom filling the log.

Adaptive Server may allocate more pages than are actually needed,so keep the value small when space is limited. These pages aredeallocated at the end of the batch.



For more information, see “number of pre-allocated extents” in theSystem Administration Guide.

Using Parallel Bulk Copy to Copy Data Into a Specific Partition

Use parallel bulk copy to copy data in parallel to a specific partition.Parallel bulk copy substantially increases performance during bcpsessions because large bulk copy jobs can be split into multiplesessions and run concurrently.

To use parallel bulk copy:

• The table into which you are copying must be partitioned.

- Use sp_helppartition to see the number of partitions on the table

- If the table is not partitioned, use alter table ptn to partition thetable.

• The table should not have indexes because:

- If the table has a clustered index, the clustered indexdetermines the physical placement of the data, and thepartition specification in the bcp command is ignored

- Slow bulk copy will be used instead of fast bulk copy if anyindexes exist

• Parallel bulk copy is likely to lead to deadlocks on index pages ifnonclustered indexes exist on the tables.

• For the best performance, each partition should reside on aseparate physical disk.

Before you copy data into your database using parallel bulk copy,you must partition the table that will contain the data.

You can use parallel bulk copy to copy in from multiple operatingsystem file using the following syntax:

bcp tablename : partition_number in file_name

Parallel bulk copy is illustrated in Figure 2-1:



Figure 2-1: Copying data into a partitioned table using parallel bulk copy

You can also use the -F and -L flags to designate the first and last rowof the same host file for each multiple parallel bulk copy session.

For information about partitioning a table, see the Adaptive ServerReference Manual or Chapter 17, “Controlling Physical DataPlacement,” in the Performance and Tuning Guide.

➤ NoteWhen using parallel bulk copy to copy data out, you cannot specify which

partitions bcp should use.

bcp in and Locks

When you copy into a table, and particularly when you copy into atable using parallel bcp, bcp acquires the following locks:

• An exclusive intent lock on the table

• An exclusive page lock on each data page, and exclusive lock onindex pages, if any indexes exist

If you are copying in very large tables, and especially if you are usingsimultaneous copies into a partitioned table, this can require a verylarge number of locks. To avoid running out of locks:

• See Chapter 11, “Setting Configuration Parameters,” in theSystem Administration Guide for more information about settingthe number of locks

Large file divided into

File1

File2

File3

File4

bcp mydb..bigtable:1 in file1 &




CopiesintoPartition 1




four smaller filesPartitioned table



• Use the -b batchsize flag to copy smaller batches (If you do not usethe -b flag, the entire copy operation is treated as single batch).

You can estimate the number of locks needed with this formula:

(# of simul. batches) X (rows_per_batch /(2016/row_length))

To see the row length for a table, you can use this query:

1> select maxlen2> from sysindexes3> where id = object_id("tablename") and

(indid = 0 or indid = 1)

Parallel Bulk Copy Methods

Use one of the following methods to copy data in using parallel bulkcopy:

• Start multiple bcp sessions in the background, specifying thepassword at the command line, using native mode, charactermode, or specifying a format file. You can start bcp as many timesas the table is partitioned.

• Start bcp in interactive mode, answer the prompts, create a formatfile, and put the process in the background when the copy begins.then issue the next bcp command, and specify the format filecreated with the first bcp.

• Start bcp sessions in multiple windows.

The syntax for using parallel bulk copy is:

bcp table_name [: partition_number ] in file_name-P mypassword

where table_name is the name of the table into which you are copyingthe data, partition_number is the partition number into which you arecopying, file_name is the host file that contains the data, andmypassword is your password.

Using Parallel Bulk Copy on Partitioned Tables

To copy sorted data in parallel into a specific partition, specify thepartition number by appending a colon and the partition number tothe table name. Split the sorted data into separate files, or use the -Fand -L flags to designate the first row and the last row, respectively, ofthe host file. bcp then copies each file or set of line numbers to aseparate partition. For example, to use parallel bulk copy to copy



sorted data into mydb..bigtable from four files into four partitions,enter:

bcp mydb..bigtable:1 in file1 -Pmypassword -c &bcp mydb..bigtable:2 in file2 -Pmypassword -c &bcp mydb..bigtable:3 in file3 -Pmypassword -c &bcp mydb..bigtable:4 in file4 -Pmypassword -c &

The partition you specify must exist before you issue the bcpcommand.

The number of parallel bulk copy sessions you can start is limited bythe number of partitions in the table. For example, if a table has fourpartitions and you start five parallel bulk copy jobs, only the firstfour jobs can run in parallel; the fifth job does not start until one ofthe four jobs finishes copying data in.

Parallel Bulk Copy and IDENTITY Columns

IDENTITY columns have the potential to cause a bottleneck whenyou are using parallel bulk copy.

When bcp generates the values of the IDENTITY column as it reads inthe data, it also updates the IDENTITY column’s maximum value foreach row, which may affect the performance improvement you canreceive from using parallel bulk copy. To avoid this bottleneck, usethe -g flag to specify a starting point for each session, ensuring thatthe range of values generated for each file do not overlap.

Then, Adaptive Server can generate a sequence of IDENTITYcolumn values for the bcp session without checking and updating themaximum value of the table’s IDENTITY column for each row.Instead, it updates the maximum value at the end of each batch.

To specify a starting IDENTITY value, enter:

bcp [-g < first_identity_column_value >]

For example, to copy in four files, each of which has 100 rows, enter:

bcp mydb..bigtable in file1 -g 100bcp mydb..bigtable in file2 -g 200bcp mydb..bigtable in file3 -g 300bcp mydb..bigtable in file4 -g 400

Using the -g parameter does not guarantee that the IDENTITYcolumn values are unique. You must know how many rows are in theinput files and what the highest existing value is so that the startingvalues you specify with the -g parameter generate ranges that do notoverlap. In the example above, if any file contains more than 100



rows, the identity values overlap the next 100 rows of data, creatingduplicate identity values. You must also make sure that no one else isinserting data that can produce conflicting IDENTITY values.

◆ WARNING!Because parallel bulk copy allows you to specify the range of identityvalues for each file you are copying in, you will create duplicateidentity values if you specify identity value ranges that overlap.

Also note that if you copy sorted data into the table withoutspecifying the -g or -E parameter, bcp may not generate the IDENTITYcolumn values in sorted order. When neither -g or -E is specified,parallel bulk copy reads the information into all the partitionssimultaneously and updates the values of the IDENTITY column asit reads in the data. The example described above would produceIDENTITY column numbers similar to those shown in Figure 2-2:

Figure 2-2: Producing IDENTITY columns in sorted order

The table has a maximum IDENTITY column number of 119, but theorder is no longer meaningful.

If you want Adaptive Server to enforce unique IDENTITY columnvalues, you must run bcp with either the -g or -E parameter.

ID column ID column ID column ID column

Partition 1 Partition 2 Partition 3 Partition 4

100 101102 103

104 105106

107

108

109

110

111

112

113

114

115116

117 118119

A

A

B

B

B

C

C

C

D

E

F

F

F

G

G

H

H

I

J

J


Using the bcp Options Adaptive Server Enterprise Release 11.5.x

Using the bcp Options

See “bcp” on page 1-7 for bcp syntax and a full discussion of theavailable options. The following sections describe some of the morecomplex options.

Using the Default Formats

bcp provides two command line options that create files withfrequently used default formats. These options provide the easiestway to copy data in and out of Adaptive Server. The -n option uses“native” (or operating system) formats. The -c option uses“character” (char datatype) for all columns, providing tabs betweenfields on a row, and a newline terminator (such as a carriage return)at the end of each row.

If you are using the native or character options, bcp operatesnoninteractively and does not ask you for any information exceptyour Adaptive Server password.

Native Format

The -n option creates files using native (operating system-specific)formats. Native format usually creates a more compact operatingsystem file. For example, the following commands copy thepublishers table to the file called pub_out, using native data format:

bcp pubs2..publishers out pub_out -n

Here are the contents of pub_out:

0736^MNew Age Books^FBoston^BMA0877^PBinnet & Hardley^JWashington^BDC1389^TAlgodata Infosystems^HBerkeley^BCA

bcp prefixed each field (except the pub_id, which is a char(4) datatype)with an ASCII character equivalent to the length of the data in thefield. For example, “New Age Books” is 13 characters, and ^M(Ctrl-M) is ASCII 13. All data in the pub_out table is char or varchardata, so it is human-readable. In a table with numeric data, bcp writesthe information to the file in the operating system’s datarepresentation format, which may not be human-readable.


Adaptive Server Enterprise Release 11.5.x Using the bcp Options

➤ NoteCopying data in native format from different operating systems (for

example, copying from NT to UNIX) is not supported. Use the -c flag if you

need to use bcp to copy files from one operating system to another.

Character Format

Character format uses the char datatype for all columns. It insertstabs between fields in each row and a newline terminator at the endof each row.

For example, the following command copies the publishers file out incharacter format:

bcp pubs2..publishers out pub_out -c

The preceding command produces this output:

0736 New Age Books Boston MA0877 Binnet & Hardley Washington DC1389 Algodata Infosystems Berkeley CA

Changing Terminators

Terminators are the characters used to separate data fields. Therow terminator is the field terminator of the last field in thetable or file. Use the -t field_terminator and -r row_terminator commandline options to change the terminators. The following example usesthe comma as the field terminator and \r (Return) as the rowterminator (remember to “escape” the backslash if necessary foryour operating system command shell):

bcp pubs2..publishers out pub_out -c -t , -r \\r

This produces:

0736,New Age Books,Boston,MA0877,Binnet & Hardley,Washington,DC1389,Algodata Infosystems,Berkeley,CA

The -t and -r options can change the default terminators without thecharacter option.


Changing the Defaults: Interactive bcp Adaptive Server Enterprise Release 11.5.x

Changing the Defaults: Interactive bcp

If you do not specify native (-n) or character (-c) format, bcp promptsinteractively for the file storage type, prefix length, and terminatorfor each column of data to be copied. For fields that are to be storedas char or binary, bcp also prompts for a field length.

The default values for the file storage, prefix length, terminator, andfield length prompts produce the same results as using the nativeformat, and provide a simple means for copying data out of adatabase for later reloading into Adaptive Server. If you are copyingdata to or from Adaptive Server for use with other programs, baseyour answers to the prompts on the format required by the othersoftware.

Your responses to these four prompts create an extremely flexiblesystem that allows you to read a file from other software or to createa file that requires little or no editing to conform to many other dataformats. The following sections discuss these prompts and the waythey interact to affect the data.

File Storage Type

The file storage type prompt offers you choices about how to storethe data in the file. You can copy data into a file as its database tabletype, as a character string, or as any datatype for which implicitconversion is supported. User-defined datatypes are copied as theirbase types.

Table 2-4 shows the default storage type for each Adaptive Serverdatatype, and the legal abbreviations. For the most compact storage,use the default value; for character files, use char. In Table 2-4,brackets [] indicate that you can use the initial character or thebeginning characters of the word; for example, for “bit” you can use“b,” “bi,” or “bit.” timestamp data is treated as binary(8). The datestorage type is the Adaptive Server internal storage format ofdatetime, not the host operating system format of the date.

Table 2-4: File storage datatypes for bcp

Table Datatype Storage Typechar, varchar c[har]text T[ext]int i[nt]smallint s[mallint]tinyint t[inyint]


Adaptive Server Enterprise Release 11.5.x Changing the Defaults: Interactive bcp

To see this list while using bcp interactively, type a question mark inresponse to the prompt “Enter the file storage type”.

The suggested values that appear in the prompts are the defaults.Remember that your response determines how the data is stored inthe output file; you need not indicate the column’s type in thedatabase table.

bcp fails if you enter a type that is not either implicitly convertibleor char. For example, you may not be able to use smallint for int data(you may get overflow errors), but you can use int for smallint.

When storing noncharacter datatypes as their database types, bcpwrites the data to the file in Adaptive Server’s internal datarepresentation format for the host operating system, rather than inhuman-readable form.

Prefix Length

By default, bcp precedes each field that has a variable storage lengthwith a string of one or more bytes indicating the length of the field.This provides the most compact file storage. The default values in theprompts indicate the most efficient prefix length.

For fixed-length fields, the prefix length should be 0.

For fields of 255 bytes or less, the default prefix length is 1. For text orimage datatypes, the default prefix length is 4. When binary andvarbinary datatypes are being converted to char storage types, thedefault prefix length is 2, since each byte of table data requires 2 bytesof file storage. See Table 2-7 on page 2-22 for more information aboutprefix lengths.

float f[loat]money m[oney]bit b[it]datetime d[atetime]binary, varbinary, timestamp ximage I[mage]smalldatetime Dreal rsmallmoney Mnumeric ndecimal e

Table 2-4: File storage datatypes for bcp (continued)

Table Datatype Storage Type



Adaptive Server stores binary, varbinary and image data as an evennumber of hexadecimal digits. For these types, use even numbers forthe prefix and length.

➤ Notebcp considers any data column that can contain null values to be a variable-

length column. This includes columns with integer datatypes that might

ordinarily be considered fixed-length columns. Use a prefix length (other

than 0) or a terminator to denote the length of each row’s data.

To store data with no prefix before its column, use a prefix length of0. bcp pads each stored field with spaces to the full length specified atthe next prompt, “length,” unless you supply a terminator.

Because prefix lengths consist of native format integers, the resultinghost file contains nonprintable characters. You may not be able toprint the host file or to transmit it using a communications programthat cannot handle non-human-readable characters.

Field Length

“Length” and “storage length” in this discussion refer to theoperating system file, not to Adaptive Server field lengths.

In almost all cases, accept the bcp default value for the storage lengthwhile copying data out. If you are making a file to reload intoAdaptive Server, the default prefixes and length keep the storagespace needed to a minimum. If you are creating a human-readablefile, use the default length so that you do not truncate the data orcreate overflow errors that cause bcp to fail.

It is possible to change the default length by supplying anothervalue. If you are copying character data in from other software,carefully examine the source file before choosing length values.

If the storage type is noncharacter, bcp stores the data in the operatingsystem’s native data representation and does not prompt for alength.

When bcp converts noncharacter data to character storage, it suggestsa default field length that is large enough to store the data withouttruncating datetime data or causing an overflow of numeric data. Thedefault lengths are the number of bytes needed to display the longest


Adaptive Server Enterprise Release 11.5.x Changing the Defaults: Interactive bcp

value for the Adaptive Server datatype. Table 2-5 lists the defaultfield lengths:

If you specify a field length that is too short for numeric data whencopying data out, bcp prints an overflow message and does not copythe data.

The default length for binary and varbinary fields is twice the lengthdefined for the column, since each byte of the field requires 2 bytes offile storage.

If you accept the default storage length, the actual amount of storagespace allocated depends on whether or not you specify a prefixlength and terminators.

• If you specify a prefix length of 1, 2, or 4, bcp uses a storage spaceof the actual length of the data, plus the length of the prefix, plusany terminators.

• If you specify a prefix length of 0 and no terminator, bcp allocatesthe maximum amount of space shown in the prompt, which is themaximum space that may be needed for the datatype in question.In other words, bcp treats the field as if it were fixed length todetermine where one field ends and the next begins. For example,if the field is defined as varchar(30), bcp uses 30 bytes for eachvalue, even if some of the values are only 1 character long. bcpdoes not know how large any one data value will be before

Table 2-5: Default field lengths for datatypes

Datatype Default Size

int 12 bytes

smallint 6 bytes

tinyint 3 bytes

float 25 bytes

money 24 bytes

bit 1 byte

datetime 26 bytes

smalldatetime 26 bytes

real 25 bytes

smallmoney 24 bytes



copying all the data, so it always pads char datatypes to their fullspecified length.

Field and Row Terminators

A terminator can be used to mark the end of a column or row,separating one from the next. The default is no terminator. Fieldterminators separate table columns; the row terminator is the fieldterminator of the last field in the row of the table or file.

Terminators are very useful for dealing with character data becauseyou can choose human-readable terminators. The bcp characteroption, which uses tabs between each column with a newlineterminator at the end of each row, is an example of using terminatorsthat enhance the readability of a data file.

When you prepare data for use with other programs, and when youwant to use bcp to prepare tabular data, supply your ownterminators. The available terminators are:

• Tabs, indicated by \t

• New lines, indicated by \n

• Carriage returns, indicated by \r

• Backslash, indicated by \

• Null terminators (no visible terminator), indicated by \0

• Any printable character (*, A, t, |, and so forth)

• Strings of up to 10 printable characters, including some or all ofthe terminators listed above (for example, **\t**, end, !!!!!!!!!!, and\t--\n)

➤ NoteControl characters (ASCII 0–25) cannot be printed.

Choose terminators with patterns that do not appear in any of thedata. For example, assume that using a tab terminator with a stringof data that contains a tab creates an ambiguity: Which tab representsthe end of the string? bcp always looks for the first possibleterminator, which in this case would be incorrect, since the first tab itwould encounter would be the one that is part of the data string.

Data in native format can also conflict with terminators. Given acolumn that contains a 4-byte integer in native format, if the values


Adaptive Server Enterprise Release 11.5.x Using Format Files

of these integers are not strictly limited, it will be impossible tochoose a terminator that is guaranteed not to appear inside the data.Use bcp’s native format option for data in native format.

Note that “no terminator” is different from a “null terminator,”which is an invisible, but real, character.

Using Format Files

After gathering information about each field in the table, bcp asks ifyou want to save a format file and prompts for the file name. Use thisformat file to copy the data back into Adaptive Server or to copy dataout from the table at another time. When you copy data in or outusing an existing format file, bcp does not prompt for information;the format file provides the information needed.

Figure 2-3 illustrates the format of the bcp format files. It shows thepublishers table from the pubs2 database, with all the host file columnsin character format, no prefix, the default data length, a newlineterminator at the end of the final column of a row, and tabs asterminators for all other columns.

Figure 2-3: bcp format file

Elements of the bcp Format File

The bcp version is always the first line of the file. It specifies whatversion of bcp you are using, not Adaptive Server. This is a literalstring without quotation marks. In Figure 2-3, the version is 11.1.

11.141 SYBCHAR 0 4 "\t" 1 pub_id2 SYBCHAR 0 20 "\t" 2 pub_name3 SYBCHAR 0 20 "\t" 3 city4 SYBCHAR 0 2 "\n" 4 state

VersionPrefixlength Terminator

Servercolumnname

Number ofcolumns

Host filecolumnorder

Host filedatatype

Host filedatalength

Servercolumnorder


Using Format Files Adaptive Server Enterprise Release 11.5.x

The second line of a bcp format file is the number of columns, whichrefers to the number of records in the format file, not including lines1 and 2. Each column in the host table has one line.

The first and second lines are followed by one line for each column inthe database table, consisting of elements usually separated by tabs,except that the host file datatype and the prefix length are usuallyseparated by a space. The following sections describe the elements inthe format file.

Host File Column Order

The host file column order is the sequential number of the field inthe host data file, starting with 1.

Host File Datatype

The host file datatype refers to the storage format of the field in thehost data file, not the datatype of the database table column.Table 2-6 lists the valid storage formats.

Prefix Length

Prefix length indicates the number of bytes in the field length prefix.The length prefix is a 0-, 1-, 2-, or 4-byte unsigned integer valueembedded in the host data file that specifies the actual length of data

Table 2-6: Host file datatype storage format

Storage Format Adaptive Server DatatypeSYBCHAR char/varchar (ASCII)SYBTEXT textSYBBINARY binarySYBIMAGE imageSYBINT1 tinyintSYBINT2 smallintSYBINT4 intSYBFLT8 floatSYBREAL realSYBBIT bitSYBNUMERIC numericSYBDECIMAL decimalSYBMONEY moneySYBMONEY4 smallmoneySYBDATETIME datetimeSYBDATETIME4 smalldatetime


Adaptive Server Enterprise Release 11.5.x Using Format Files

contained in the field. Some fields may have a length prefix whileothers do not.

Table 2-7 shows the allowable prefix length values.

Host File Data Length

Host file data length refers to the maximum number of bytes to copyfor the field. bcp uses either the maximum field length, the prefixlength (if any), or the field terminator string (if any) to decide howmuch data to copy in or out. If more than one method of field lengthspecification is given, bcp chooses the one that copies the leastamount of data.

Terminator

The terminator can be up to 30 bytes of characters enclosed inquotation marks (" "). The terminator designates the end of data forthe host data file field.

Server Column Order

Together, the host file column order and the server column ordermap host data file fields to the database table columns. The value inthis field represents the colid of the syscolumns column into which thehost data file column is to be loaded.

Server Column Name

The server column name is the name of the database table columninto which this field is to be loaded.

Table 2-7: Allowable prefix length values

Length (inbytes) Range

0 No prefix1 28-1; 0–2552 216-1; 0–655354 232 -1; 0–4,294,967,295


Examples: Copying Out Data Interactively Adaptive Server Enterprise Release 11.5.x

Column Precision

The column precision is the precision of the database table columninto which this field is to be loaded. This element is present only ifthe storage format is numeric or decimal.

Column Scale

The column scale is the scale of the database table column into whichthis field is to be loaded. This element is present only if the storageformat is numeric or decimal.

Examples: Copying Out Data Interactively

By changing the default values of the prompts to bcp, you canprepare data for use with other software. To create a human-readablefile, respond to the bcp prompts as follows:

• File storage type – Enter "0"

• Prefix length – Enter "0"

• Field length – Enter "default"

• Terminator – The field terminator you enter depends on thesoftware you plan to use. Choose between delimited fields orfixed-length fields. Always use “\n”, the newline terminator, toterminate the last field.

For fixed-length fields, do not use a terminator. Each field has afixed length, with spaces to pad the fields. Adjacent fields, wherethe data completely fills the first field seem to run together, sincethere are no field separators on each line of output. See theexample below.

For comma-delimited output, use a comma as the terminator foreach field. To create tabular output, use the tab character, “\t”.

Copying Out Data with Field Lengths

The following example uses fixed-length fields to create output inthe personal computer format called SDF (system data format). Thisformat can be easily read or produced by other software.


Adaptive Server Enterprise Release 11.5.x Examples: Copying Out Data Interactively

bcp pubs2..sales out sal_out

Password:

Enter the file storage type of field stor_id [char]:Enter prefix-length of field stor_id [0]:Enter length of field stor_id [4]:Enter field terminator [none]:

Enter the file storage type of field ord_num [char]:Enter prefix-length of field ord_num [1]: 0Enter length of field ord_num [20]:Enter field terminator [none]:

Enter the file storage type of field date [datetime]: charEnter prefix-length of field date [1]: 0Enter length of field date [26]:Enter field terminator [none]: \n

Do you want to save this format information in a file? [Y/n] yHost filename [bcp.fmt]: sal_fmt

Starting copy...


The results in the sal_out file are as follows:

5023 AB-123-DEF-425-1Z3 Oct 31 1985 12:00AM5023 AB-872-DEF-732-2Z1 Nov 6 1985 12:00AM5023 AX-532-FED-452-2Z7 Dec 1 1990 12:00AM5023 BS-345-DSE-860-1F2 Dec 12 1986 12:00AM5023 GH-542-NAD-713-9F9 Mar 15 1987 12:00AM5023 NF-123-ADS-642-9G3 Jul 18 1987 12:00AM5023 XS-135-DER-432-8J2 Mar 21 1991 12:00AM5023 ZA-000-ASD-324-4D1 Jul 27 1988 12:00AM5023 ZD-123-DFG-752-9G8 Mar 21 1991 12:00AM5023 ZS-645-CAT-415-1B2 Mar 21 1991 12:00AM5023 ZZ-999-ZZZ-999-0A0 Mar 21 1991 12:00AM6380 234518 Sep 30 1987 12:00AM6380 342157 Dec 13 1985 12:00AM6380 356921 Feb 17 1991 12:00AM7066 BA27618 Oct 12 1985 12:00AM7066 BA52498 Oct 27 1987 12:00AM7066 BA71224 Aug 5 1988 12:00AM7067 NB-1.142 Jan 2 1987 12:00AM7067 NB-3.142 Jun 13 1990 12:00AM7131 Asoap132 Nov 16 1986 12:00AM


Examples: Copying Out Data Interactively Adaptive Server Enterprise Release 11.5.x

7131 Asoap432 Dec 20 1990 12:00AM7131 Fsoap867 Sep 8 1987 12:00AM7896 124152 Aug 14 1986 12:00AM7896 234518 Feb 14 1991 12:00AM8042 12-F-9 Jul 13 1986 12:00AM8042 13-E-7 May 23 1989 12:00AM8042 13-J-9 Jan 13 1988 12:00AM8042 55-V-7 Mar 20 1991 12:00AM8042 91-A-7 Mar 20 1991 12:00AM8042 91-V-7 Mar 20 1991 12:00AM

The contents of the sal_fmt format file are as follows:

11.131 SYBCHAR 04 "" 1 stor_id2 SYBCHAR 020 "" 2 ord_num3 SYBCHAR 026 "" 3 date

Copying Out Data with Delimiters

In the following examples, bcp copies data interactively from thepublishers table to a file.

The first example creates an output file with commas between allfields in a row and a newline terminator at the end of each row. Thisexample creates a format file (pub_fmt) that you can use later to copythe same or similar data back into Adaptive Server.

bcp pubs2..publishers out pub_out

Password:

Enter the file storage type of field pub_id [char]:Enter prefix length of field pub_id [0]:Enter length of field pub_id [4]:Enter field terminator [none]: ,

Enter the file storage type of field pub_name [char]:Enter prefix length of field pub_name [1]: 0Enter length of field pub_name [40]:Enter field terminator [none]: ,

Enter the file storage type of field city [char]:Enter prefix length of field city [1]:0Enter length of field city [20]:Enter field terminator [none]: ,


Adaptive Server Enterprise Release 11.5.x Examples: Copying Out Data Interactively

Enter the file storage type of field state [char]:Enter prefix length of field state [1]: 0Enter length of field state [2]:Enter field terminator [none]: \n

Do you want to save this format information in a file? [Y/n] yHost filename [bcp.fmt]: pub_fmt

Starting copy...


The results in the pub_out file are as follows:

0736,New Age Books,Boston,MA0877,Binnet & Hardley,Washington,DC1389,Algodata Infosystems,Berkeley,CA

The contents of the pub_fmt format file are as follows:

11.141 SYBCHAR 0 4 "," 1 pub_id2 SYBCHAR 0 40 "," 2 pub_name3 SYBCHAR 0 20 "," 3 city4 SYBCHAR 0 2 "\n" 4 state

Similarly, the following example creates tab-delimited output fromthe table pubs2..publishers in the pub_out file.

bcp pubs2..publishers out pub_out

Password:

Enter the file storage type of field pub_id [char]:Enter prefix-length of field pub_id [0]: 0Enter length of field pub_id [4]:Enter field terminator [none]: \t

Enter the file storage type of field pub_name [char]:Enter prefix-length of field pub_name [1]: 0Enter length of field pub_name [40]:Enter field terminator [none]: \t

Enter the file storage type of field city [char]:Enter prefix-length of field city [1]: 0Enter length of field city [20]:Enter field terminator [none]: \t

Enter the file storage type of field state [char]:Enter prefix-length of field state [1]: 0


Examples: Copying In Data Interactively Adaptive Server Enterprise Release 11.5.x

Enter length of field state [2]:Enter field terminator [none]: \n

Do you want to save this format information in a file? [Y/n] yHost filename [bcp.fmt]: pub_fmt

Starting copy...


The results in the pub_out file are as follows:

0736 New Age Books Boston MA0877 Binnet & Hardley Washington DC1389 Algodata Infosystems Berkeley CA

The contents of the pub_fmt format file are as follows:

11.141 SYBCHAR 04 "\t" 1 pub_id2 SYBCHAR 040 "\t" 2 pub_name3 SYBCHAR 020 "\t" 3 city4 SYBCHAR 02 "\n" 4 state

Examples: Copying In Data Interactively

To copy data successfully into a table from a file, you must knowwhat the terminators in the file are, or what the field lengths are, andspecify them when you use bcp. The following examples show howto copy data (either with fixed field lengths or with delimiters) inusing bcp, with or without a format file.

Copying In Data with Field Lengths

In the following example, bcp copies data from the salesnew file intothe pubs2..sales table. In the salesnew file are three fields: the first is 4characters long, the second is 20, and the third is 26 characters long.Each row ends with a newline terminator (\n), as follows:

5023ZS-731-AAB-780-2B9 May 24 1993 12:00:00:000AM5023XC-362-CFB-387-3Z5 May 24 1993 12:00:00:000AM6380837206 May 24 1993 12:00:00:000AM6380838441 May 24 1993 12:00:00:000AM


Adaptive Server Enterprise Release 11.5.x Examples: Copying In Data Interactively

Use the following command to copy in the data interactively fromsalesnew:

bcp pubs2..sales in salesnew

The system responds as follows:

Password:

Enter the file storage type of field stor_id [char]:Enter prefix-length of field stor_id [0]:Enter length of field stor_id [4]:Enter field terminator [none]:

Enter the file storage type of field ord_num [char]:Enter prefix-length of field ord_num [1]: 0Enter length of field ord_num [20]:Enter field terminator [none]:

Enter the file storage type of field date [datetime]: charEnter prefix-length of field date [1]: 0Enter length of field date [26]:Enter field terminator [none]: \n

Do you want to save this format information in a file? [Y/n] yHost filename [bcp.fmt]: salesin_fmt

Starting copy...


When you log into Adaptive Server and access sales, you will see thefollowing data from salesnew appended to the table:

select * from sales

stor_id ord_num date------- -------------------- --------------------------5023 AB-123-DEF-425-1Z3 Oct 31 1985 12:00AM5023 AB-872-DEF-732-2Z1 Nov 6 1985 12:00AM5023 AX-532-FED-452-2Z7 Dec 1 1990 12:00AM5023 BS-345-DSE-860-1F2 Dec 12 1986 12:00AM5023 GH-542-NAD-713-9F9 Mar 15 1987 12:00AM5023 NF-123-ADS-642-9G3 Jul 18 1987 12:00AM5023 XS-135-DER-432-8J2 Mar 21 1991 12:00AM5023 ZA-000-ASD-324-4D1 Jul 27 1988 12:00AM5023 ZD-123-DFG-752-9G8 Mar 21 1991 12:00AM5023 ZS-645-CAT-415-1B2 Mar 21 1991 12:00AM5023 ZZ-999-ZZZ-999-0A0 Mar 21 1991 12:00AM6380 234518 Sep 30 1987 12:00AM6380 342157 Dec 13 1985 12:00AM6380 356921 Feb 17 1991 12:00AM


Examples: Copying In Data Interactively Adaptive Server Enterprise Release 11.5.x

7066 BA27618 Oct 12 1985 12:00AM7066 BA52498 Oct 27 1987 12:00AM7066 BA71224 Aug 5 1988 12:00AM7067 NB-1.142 Jan 2 1987 12:00AM7067 NB-3.142 Jun 13 1990 12:00AM7131 Asoap132 Nov 16 1986 12:00AM7131 Asoap432 Dec 20 1990 12:00AM7131 Fsoap867 Sep 8 1987 12:00AM7896 124152 Aug 14 1986 12:00AM7896 234518 Feb 14 1991 12:00AM8042 12-F-9 Jul 13 1986 12:00AM8042 13-E-7 May 23 1989 12:00AM8042 13-J-9 Jan 13 1988 12:00AM8042 55-V-7 Mar 20 1991 12:00AM8042 91-A-7 Mar 20 1991 12:00AM8042 91-V-7 Mar 20 1991 12:00AM(34 rows affected)

Since there is a unique clustered index on the stor_id and ord_numcolumns of sales, the new rows were sorted into order. Had therebeen any violations of the unique index on the columns in the databeing copied from the file, bcp would have discarded the entire batchthat contained a violating row. (A batch size of 1 evaluates each rowindividually, but loads more slowly and creates a separate data pagefor each row during a fast bcp session) If the types copied in areincompatible with the database types, the entire copy fails.

Copying In Data with Delimiters

In the following example, bcp copies data from the file newpubs intothe table pubs2..publishers. In the newpubs file, each field in a row endswith a tab character (\t) and each row ends with a newlineterminator (\n), as follows:

1111 Stone Age Books Boston MA2222 Harley & Davidson Washington DC3333 Infodata Algosystems Berkeley CA

Since newpubs contains all character data, you can use the charactercommand line flag and specify the terminators with command lineoptions as follows:

bcp pubs2..publishers in newpubs -c -t \\t -r \\n


Adaptive Server Enterprise Release 11.5.x Using bcp with Alternate Languages

Copying In Data with a Format File

To copy data back into Adaptive Server using the saved pub_fmtformat file, use the following command:

bcp pubs2..publishers in pub_out -f pub_fmt

You can use the pub_fmt file to copy any data with the same formatinto Adaptive Server. If you have a similar data file with differentdelimiters, you can change the delimiters in the format file.

Similarly, you can edit the format file to reflect any changes to thefield lengths, as long as all fields have the same length. For example,the moresales file contains the following:

804213-L-9 Jan 21 1993 12:00AM804255-N-8 Mar 12 1993 12:00AM804291-T-4 Mar 23 1993 12:00AM804291-W-9 Mar 23 1993 12:00AM

Edit the sal_fmt format file to read as follows:

11.531 SYBCHAR 0 4 "" 1 stor_id2 SYBCHAR 0 7 "" 2 ord_num3 SYBCHAR 0 21 "\n" 3 date

Then enter the following command:

bcp pubs2..sales in moresales -f sal_fmt

The system responds as follows:

Starting copy...


Using bcp with Alternate Languages

Adaptive Server stores data using its default character set, which isconfigured during installation. If your terminal does not support thatdefault character set, it may send confusing characters to bcp whenyou respond to prompts by typing or by using host file scripts.

Omitting all character set options sets the character set to a defaultfor the platform. (This is not necessarily the same one that wasconfigured for Adaptive Server.) Note also that this default may notnecessarily be the character set that the client is using. (See“Configuring Client/Server Character Set Conversions” in the


Copy In and Batches Adaptive Server Enterprise Release 11.5.x

System Administration Guide for more information about charactersets and the associated flags.)

Copy In and Batches

Batching applies only to bulk copying in; it has no effect whencopying out. By default, Adaptive Server copies all the rowsspecified in one batch. Use the command line option to specify abatch size.

When data is being copied in, it can be rejected by either AdaptiveServer or bcp. Adaptive Server treats each batch as a separatetransaction. If the server rejects any row in the batch, the entiretransaction rolls back. bcp then continues to the next batch. Only fatalerrors roll back the transaction.

bcp saves its error messages to an error file (for example, whenAdaptive Server encounters a duplicate row for a table that has aunique index). Adaptive Server generates error messages on a batch-by-batch basis, instead of row-by-row, and rejects each batch inwhich it finds an error. Error messages appear on your terminal andin the error file.

You can break large input files into smaller units for betterrecoverability. For example, if 300,000 rows are bulk copied in with abatch size of 100,000 rows, and there is a fatal error after row 200,000,the first two batches—200,000 rows—will have been successfullycopied into Adaptive Server. If batching had not been used, no rowswould have been copied into Adaptive Server.

The log entry for the transaction is available for truncation after thebatch completes. If you copy into a database that has the trunc log onchkpt database option set on (true), the next automatic checkpointremoves the log entries for completed batches. This breaks up largebcp operations and keeps the log from filling.

You can even set the batchsize to 1, which causes only the defectiverow to be rejected. This allows you to identify exactly which rowfailed. However, this loads slowly and takes up storage space.Because bcp creates 1 data page per batch, setting batchsize to 1 createsdata pages with 1 row on each page.

Batches and Partitioned Tables

When you bulk copy data into a partitioned table without specifyinga partition number, Adaptive Server randomly assigns each batch to


Adaptive Server Enterprise Release 11.5.x Copy Out and text and image Data

an available partition. Copying rows in a single batch places all thoserows in a single partition, which can lead to load imbalance in thepartitioned table. To help keep partitioned tables balanced, use asmall batch size when bulk copying data or specify the partition idduring the bcp session. See “Bulk Copying Data into PartitionedTables” on page 2-6 of this manual, or see the Performance and TuningGuide, for information about partitioning tables.

Copy Out and text and image Data

When you copy out text or image data, by default Adaptive Servercopies only the first 32K of data in a text or image field. The -T flagallows you to specify a different value. For example, if the text fieldto copy out contains up to 40K of data, copy out all 40K with thefollowing command:

bcp pubs2..publishers out -T 40960

If a text or image field is larger than the given value or the default, theremaining data is not copied out.

Specifying a Network Packet Size

You may want to use larger network packet sizes than the defaults toimprove the performance of large bulk copy operations. The -A sizeoption specifies the network packet size to use for a bcp session. sizemust be between the values of the default network packet size and maxnetwork packet size configuration parameters, and it must be a multipleof 512. The new packet size is in effect for that bcp session only. Forexample:

bcp pubs2..authors out -A 2048 -T 40960

specifies that Adaptive Server send 40K of text or image data using apacket size of 2048 bytes for this bcp session.

Copy In and Error Files

The error file stores a line indicating which row failed and what erroroccurred, and a line that is an exact copy of the row from the host file.If the file name specified after the -e already exists, bcp overwrites thisfile. If bcp does not encounter any errors, it does not create the file.

bcp in detects two types of errors:

• Data conversion errors


Copy Out and Error Files Adaptive Server Enterprise Release 11.5.x

• Errors in building the row, such as attempts to insert a NULL intocolumns that do not accept them or to use invalid data formats(such as a 3-byte integer)

Error messages appear on your monitor. The following exampleloads the newpubs file into the publishers database, storing any errorrows in the pub_err file:

bcp pubs2..publishers in newpubs -e pub_err

bcp stores rows in an error file only when the bcp program itselfdetects the error, and continues to copy rows until bcp encounters themaximum number of error rows, at which point bcp stops the copy.

bcp sends rows to Adaptive Server in batches, so bcp cannot savecopies of rows that are rejected by Adaptive Server (for example, aduplicate row for a table that has a unique index). Adaptive Servergenerates error messages on a batch-by-batch basis, instead of row-by-row, and rejects the entire batch if it finds an error.

It is not considered an error for Adaptive Server to reject duplicaterows if allow_dup_row or ignore_dup_key was set when a table’s indexwas created. The copy proceeds normally, and the duplicate rows arenot stored in the table nor in the bcp error file.

Copy Out and Error Files

As with copy in, bcp overwrites any file of the same name, and doesnot create the file if no errors occurred.

There are two situations that cause rows to be logged in the error fileduring a copy out:

• A data conversion error in one of the row’s columns

• An I/O error in writing to the host file

bcp logs rows in the error file in the default character format. All datavalues print as characters, with tabs between the columns and anewline terminator at the end of each row.

Data Integrity: Defaults, Rules, and Triggers

When copying data into a table, bcp observes any defaults defined forthe columns and datatypes. That is, if there is a null field in the datain a file, bcp loads the default value instead during the copy. Forexample, here are two rows in a file to be loaded into authors:


Adaptive Server Enterprise Release 11.5.x How bcp Differs from Other Utilities

409-56-7008,Bennet,Abraham,415 658-9932,6223 BatemanSt.,Berkeley,CA,USA,94705213-46-8915,Green,Marjorie,,309 63rd St.#411,Oakland,CA,USA,94618

Commas separate the fields; a newline terminator separates therows. Note that there is no phone number for Marjorie Green.Because the phone column of the authors table has a default of“unknown,” the rows in the loaded table look like this:

409-56-7008 Bennet Abraham 415 658-9932 6223 Bateman St. Berkeley CA USA 94705213-46-8915 Green Marjorie unknown 309 63rd St. #411 Oakland CA USA 94618

In order to load data at the maximum speed, bcp does not fire rulesand triggers. To find any rows that violate rules and triggers, copythe data into the table and run queries or stored procedures that testthe rule or trigger conditions.

How bcp Differs from Other Utilities

The bcp utility, which copies entire tables or portions of a single table,is distinct from commands that also move data from one place toanother such as:

• The SQL commands dump database, load database, dump transaction,and load transaction. These are used for backup purposes only.Unlike bcp, the dump commands create a physical image of theentire database. Data dumped with dump database or dumptransaction can be read only by using load database or load transaction.(See the System Administration Guide for information on using thedump and load commands. Also, see the Adaptive Server ReferenceManual.)

• The data modification commands insert, update, and delete. Usethese to add new rows to, change existing rows in, or removerows from, a table or view. You can also use the insert commandwith a select statement to move data between tables. The selectstatement with an into clause can create a new table, based on thecolumns in the select statement and the tables in the from clause,and copy the rows specified in the where clause. (See insert, update,and delete in the Adaptive Server Reference Manual for details onadding, changing, and deleting data.)


How bcp Differs from Other Utilities Adaptive Server Enterprise Release 11.5.x


3 Using the isql Utility 3.

This chapter describes the interactive SQL utility, isql.

Topics covered are:

• How to Use Transact-SQL with the isql Utility 3-1

• Changing the Command Terminator 3-4

• Performance Statistics Interaction with Command TerminatorValues 3-5

• Setting the Network Packet Size 3-6

• Input and Output Files 3-6

See “isql” on page 1-36 for the isql syntax and the available options.

How to Use Transact-SQL with the isql Utility

You can use SQL directly from the operating system with thestandalone utility program isql. You must have an account, or login,on Adaptive Server. To access the account, enter this command atyour operating system prompt:

isql

The following prompt appears:

Password:

Type your password at the prompt and press the Return key. Thepassword does not appear on the screen as you type. The isql promptappears:

1>

You can now start issuing Transact-SQL commands. The isql programsends the commands to Adaptive Server, formatting the results andprinting them to standard output. There is no maximum size for anisql statement.

3-2 Using the isql Utility

How to Use Transact-SQL with the isql Utility Adaptive Server Enterprise Release 11.5.x

To terminate a command, type the default command terminator “go”on a new line. For example:

isqlPassword:

1> use pubs22> go1> select *2> from authors3> where city = "Oakland"4> go

To exit isql, type “quit” or “exit” on a line by itself.

Formatting isql Output

Table 3-1 describes the command line options that change the formatof isql output:

To include each command issued to isql in the output, use the -eoption; use the -n option to remove numbering and prompt symbols.For example:

use pubs2goisql -e -n -o outputPassword:

select *from authorswhere city = "Oakland"goquit

cat output

Table 3-1: Format options for isql

Option Default Meaning

-h headers 1 Number of rows to print betweencolumn headings

-s colseparator Single space Changes the column separatorcharacter

-w columnwidth 80 characters Changes the line width


Adaptive Server Enterprise Release 11.5.x How to Use Transact-SQL with the isql Utility

select *from authorswhere city = "Oakland"au_id au_lname au_fnamephone addresscity state country postalcode----------- ---------------------------------------- -------------------------------- ------------------------------------------------------------ ----- ------------ ----------213-46-8915 Green Marjorie

415 986-7020 309 63rd St. #411Oakland CA USA 94618

274-80-9391 Straight Dick415 834-2919 5420 College Av.Oakland CA USA 94609

724-08-9931 Stringer Dirk415 843-2991 5420 Telegraph Av.Oakland CA USA 94609

724-80-9391 MacFeather Stearns415 354-7128 44 Upland Hts.Oakland CA USA 94612

756-30-7391 Karsen Livia415 534-9219 5720 McAuley St.Oakland CA USA 94609

Note that the output file does not include the command terminator.

Correcting Input

If you make an error when typing a Transact-SQL command, you cando one of the following:

• Press Ctrl-c or type the word “reset” on a line by itself

This clears the query buffer and returns the isql prompt.

• Type the name of your text editor on a line by itself

This puts you in a text file where you can edit the query. Whenyou write and save the file, you are returned to isql. The queryappears; type “go” to execute it.


Changing the Command Terminator Adaptive Server Enterprise Release 11.5.x

set Options That Affect Output

Table 3-2 lists the set options that affect Transact-SQL output. Formore information, see set in the Adaptive Server Reference Manual.

Changing the Command Terminator

If you include the command terminator argument (-c), you canchoose your own terminator symbol; go is the default value for thisoption. Always enter the command terminator without blanks ortabs in front of it.

Table 3-2: set options that affect Transact-SQL output

set Option Default Meaning

char_convert Off Turns character set conversion off and onbetween Adaptive Server and a client; also startsa conversion between the server character set anda different client character set.

fipsflagger Off Warns when any Transact-SQL extensions toentry level SQL92 are used.

flushmessage Off Sends messages as they are generated.

language us_english Sets the language for system messages.

nocount Off Turns off report of number of rows affected.

noexec Off Compiles each query but does not execute it;often used with showplan.

parseonly Off Checks the syntax of queries and returns errormessages without compiling or executing thequeries.

showplan Off Generates a description of the processing plan fora query; does not print results when used inside astored procedure or trigger.

statistics iostatistics time

Off Displays performance statistics after eachexecution.

statisticssubquerycache

Off Displays the number of cache hits, misses, androws in the subquery cache for each subquery.

textsize 32K Controls the number of bytes of text or image datareturned.


Adaptive Server Enterprise Release 11.5.x Performance Statistics Interaction with Command Terminator Values

For example, to use a period as the command terminator, invoke isqlas follows:

isql -c.

A sample isql session with this command terminator looks like this:

1> select name from sysusers2> .

name-----------sandykimleslie

(3 rows affected)

Using the isql command terminator option with scripts requiresadvance planning:

• Adaptive Server-supplied scripts, such as installmaster, use “go”.Do not change the command terminator for any session that usesthese scripts.

• Your own scripts may already have “go” in them. Remember toupdate your scripts to include the terminator you plan to use.

Performance Statistics Interaction with Command Terminator Values

isql provides a performance statistics option (-p). For example:

isql -p

1> select * from sysobjects2> go

returns the following statistics:

1 xact:Clock Time (ms.): total = 2000 avg = 2000 (0.50 xacts per sec.)

This means that a single transaction took 2000 ms; so the average isone transaction per 2000 ms. The clock time value reflects the entiretransaction, which starts when Client-Library builds the query andends when Client-Library returns the information from AdaptiveServer.

You can gather performance statistics based on the execution of oneor more transactions. To gather statistics on more than one


Setting the Network Packet Size Adaptive Server Enterprise Release 11.5.x

transaction, specify a number after the command terminator. Forexample, the following command:

isql -p

1> select * from sysobjects2> go 3

instructs Adaptive Server to execute three select * transactions andreport the performance statistics. Adaptive Server returns:

3 xacts:Clock Time (ms.): total = 1000 avg = 333 (3.00 xacts per sec.)

Setting the Network Packet Size

Setting the correct network packet size can greatly increase theperformance of Adaptive Server.

Setting the Network Packet Size for the Client

The -A size option specifies the network packet size to use for an isqlsession. For example:

isql -A 2048

sets the packet size to 2048 bytes for this isql session. To check yournetwork packet size, type:

select * from sysprocesses

The value for this isql session appears under the network_pktszheading in the sysprocesses table.

See Chapter 11, “Setting Configuration Parameters,” in the SystemAdministration Guide for more information about setting the networkpacket size.

Input and Output Files

You can specify input and output files on the command line with the-i and -o options.

isql does not provide formatting options for the output. However,you can use the -n option to eliminate the isql prompts and other toolsto reformat the output.

If you use the -e option, isql echoes the input to output. The resultingoutput file contains both the queries and their results.


Adaptive Server Enterprise Release 11.5.x Input and Output Files

You can specify input and output files on the command line with the/input and /output options.

isql does not provide formatting options for the output. However,you can use the /noprompt option to eliminate the isql prompts, and useother tools to reformat the output.

If you use the /echo option, isql echoes the input to output. Theresulting output file contains both the queries and their results.

UNIX Command Line Redirection

The UNIX redirection symbols, “<” and “>”, provide a similarmechanism to the -i and -o options, as follows:

isql -Usa < input > output

You can direct isql to take input from the terminal, as shown in thefollowing example:

isql -Usa -P password -S server_name << EOF > outputuse pubs2goselect * from tablegoEOF

“<<EOF” instructs isql to take input from the terminal up to the string“EOF.” You can replace “EOF” with any character string. Similarly,the following example signals the end of input with Ctrl-d:

isql -Usa << > output


Input and Output Files Adaptive Server Enterprise Release 11.5.x

Utility Programs for UNIX Platforms Index-1

Index

Symbols!! (exclamation points) operating system

commands prefix (isql) 1-36, 1-42" " (quotation marks) for enclosing

special characters 1-1"sa" account, buildmaster -m and 1-25:r (interactive isql) 1-42< (redirect in), in isql 1-38, 3-7> (redirect out), in isql 1-39, 3-7\ (backslash)

data field terminator (bcp) 2-20escaping special characters 1-1, 1-12

\0 (null) data field terminator (bcp) 1-19\n (newline) data field terminator

(bcp) 1-19\t (tab) data field terminator (bcp) 1-19

AAdaptive Server

isql parser to 1-36 to 1-43showserver information on 1-47

Aliases, overriding isql 1-42allow_dup_row option to create index, and

bcp 2-35allow updates to system tables configuration

parameter, startserver and 1-58Application programs

copying data for 2-20copying data from 2-25

ascii_7 character setbcp and 1-12defncopy and 1-30isql and 1-37

ASCII format, bcp and 2-2, 2-15

BBackslash (\)

data field terminator (bcp) 1-20, 2-20

escaping special characters 1-1, 1-12Backup Server 1-4 to 1-6

error log file 1-4interfaces file 1-6network connections 1-5runserver file and 1-5server connections, number

required 1-4showserver information on 1-47trace flags 1-5

backupserver utility command 1-4 to 1-6interfaces file 1-6network connections and 1-5startserver and 1-5version number 1-5

batchsize option, bcp 2-33bcp (bulk copy utility) 1-7 to 1-22, 2-1 to

2-36alternate languages and 2-32ASCII format and 2-2batch operations 2-33binary format and 2-2character format 2-15character sets 1-12copying data for other software 2-16,

2-25copying data in 2-6 to 2-8, 2-29 to 2-32copying data in parallel 2-9 to 2-14copying data out 2-25 to 2-29, 2-34copying to or from operating system

files 1-7 to 1-22data integrity 2-35defaults for prompts 2-16 to 2-21default values for data 2-35drop index command and 1-18drop trigger command and 1-18dump database command and 1-17,

1-18, 2-36dump transaction command and 1-17,

2-5errors allowed 1-11

Index-2


fast version 1-17, 2-4, 2-5field lengths 2-15, 2-19field terminators 1-18, 1-19, 2-16, 2-20

to 2-21file storage type 1-19, 2-17 to 2-18filters, character set input 1-11float datatype and 1-21format files 1-10, 2-21-g parameter 2-14IDENTITY columns and 1-8, 2-12 to

2-14image data copying out 2-34indexes and 2-4 to 2-6insert command and 2-3, 2-36interactive mode 1-12, 2-16Japanese character sets in 1-12load database command and 2-36load transaction command and 2-36money datatype and 1-21native file format and 1-9, 2-14native format option 2-15, 2-21non-interactive 2-15non-iso_1 data files and 2-32null character terminator and 2-21Null columns and 1-21null field terminator and 1-19null values and 2-18other Adaptive Server facilities

and 2-36partitioned tables and 2-6, 2-33performance issues 1-18, 2-4 to 2-14,

2-36permissions needed 2-3prefix length 1-19, 2-18prompts and responses 1-18, 2-16 to

2-21recoverability and 2-4row terminators 2-16, 2-20 to 2-21rules and copying data 1-17, 2-36SDF files and 2-25select into/bulkcopy/pllsort option

and 1-17select into/bulkcopy option and 2-5slow version 2-4 to 2-6

sp_dboption and 2-5sp_dboption stored procedure and 1-17storage length 2-16 to 2-20table defaults and copying data 2-35text data copying out 2-34triggers and data copying 2-5, 2-36version number 1-13

bcp.fmt files 1-10Binary data 2-2, 2-18Buffer, query 1-42, 3-3buildmaster utility command 1-23 to 1-25

creating the master database and 1-23model database and 1-23-m parameter and "sa" account 1-25

Bulk copying. See bcp (bulk copy utility)

CCalling an editor (isql) 1-36, 1-42Carriage return (\r) data field

terminator (bcp) 1-20, 2-20Chained transactions 1-37Changing data with Adaptive Server

commands 2-36Character format files (bcp) 2-14

terminators for 2-20Characters, field and row terminator

(bcp) 2-20, 2-21Character set conversion from

noncharacter data 2-19Character sets

bcp and 1-12compatibility and ascii_7 1-12defncopy utility command 1-30 to 1-32iso_1 2-32isql and 1-39Japanese 1-12langinstall 1-44platform default 1-11, 1-39, 2-32

char datatype, and bcp 1-8, 1-20, 2-14Clearing existing query buffer 1-42Columns

bcp specifications on 2-17 to 2-21, 2-35datatype sizes and 2-19



default value 2-35fixed- and variable-length 2-18null 2-35separator character (isql) 3-2text or image 2-18

Comma-delimited output 2-25, 2-27Command buffer, reading operating

system files into (isql) 1-40, 1-42Command terminator (isql) 3-2

changing 1-38default 1-36multiple executions 1-41statistics option interaction 3-5

common.loc localization file 1-44Configuration parameters

configuration file 1-26dataserver and 1-27

Configuring Adaptive Serversybsetup utility command and 1-3,

1-63Conventions, syntax xii to xiiiConversion of datatypes (bcp) 1-21Copy in 2-29 to 2-32

See also bcp (bulk copy utility)delimiters 2-31field lengths 2-29parallel bcp 2-9 to 2-14steps 2-6

Copyingdefinitions with defncopy 1-29 to 1-33invoked from the operating

system 1-29 to 1-33tables with bcp 1-7 to 1-22tables with no indexes or triggers 1-17

Copying, bulk. See bcp (bulk copy utility)Copy out 2-25 to 2-29

See also bcp (bulk copy utility)delimiters 2-27error files and 2-35fixed-length fields 2-25for other software 2-25text and image data 2-34

Copyright messagebackupserver 1-5

bcp 1-13buildmaster 1-24, 1-27, 1-40defncopy 1-31, 1-34dsedit 1-35langinstall 1-45

create index command, bcp and duplicaterows 2-35

Current Adaptive Servers and BackupServers, showing 1-47

DData

copying 2-7 to 2-8default values for missing 2-35float, isql 1-42flushing by bcp 1-20importing 2-2native (operating system) format 1-9real, isql 1-42

Database files, transferring. See bcp (bulkcopy utility); Format files (bcp)

Database management systems,other 2-2, 2-25

Database objectscopying using bcp 1-7 to 1-22copying using defncopy 1-32

Databases, copying with bcp 2-1 to 2-36Data conversion errors 2-34, 2-35Data copying. See bcp (bulk copy utility);

Copy in; Copy outData copying steps. See Copy in; Copy

outData files, transferring. See bcp (bulk

copy utility); Format files (bcp)Data parsing (isql) 1-36 to 1-43dataserver utility command 1-26

master database and 1-26Data transfer 2-2Datatypes

bcp field lengths 2-17 to 2-20bcp file storage types for 2-16 to 2-18bcp format files for 2-21copying and compatibility 2-31

Index-4


defaults and bcp prompts 1-18money 1-21storage (SYB types) 2-23storage length in bcp 1-20

datetime datatype, and bcp 1-21default network packet size configuration

parameter 1-38, 2-34Defaults

bcp data conversion 2-19bcp prompts 2-16 to 2-21copying into tables using data 2-35copying with defncopy 1-29 to 1-33

defncopy utilitycreate statements 1-32

defncopy utility command 1-29 to 1-33in file or out file name 1-32version number 1-31, 1-34

Deleting obsolete error messages usinglanginstall 1-45

Delimiterscopy in with 2-31copy out with 2-27

Disk mirroring, dataserver and 1-23, 1-26drop index command, bcp and 1-18Dropping indexes before copying

data 2-5drop trigger command, bcp and 1-18dsedit utility command

version number 1-35DSLISTEN environment variable 1-6,

1-28DSQUERY environment variable 1-13dump database command

bcp and 1-18, 2-36dump transaction and 2-4

dump databasecommandfast bcp and 1-17

Dumping compared to bulkcopying 2-36

dump transaction commandbcp and 2-36dump database and 2-4fast bcp and 1-17select into/bulkcopy/pllsort and 1-18

EEcho input (isql) 3-6, 3-7Encryption, password. See Password

encryptionEnvironment variable

DSLISTEN 1-6, 1-28DSQUERY 1-13LANG 1-5LC_ALL 1-5

Error logBackup Server 1-4dataserver and 1-26startserver and 1-59

Error messagesisql user-defined 1-39langinstall updates of 1-45

Errorscharacter conversion 1-37maximum bcp copying 1-11

Exchange files. See Format files (bcp)Exchanging data files. See bcp (bulk copy

utility)Exclamation points (!!) operating system

commands prefix (isql) 1-36, 1-42exit command, isql 1-34, 1-36, 3-2Exporting data. See bcp (bulk copy

utility); Copy out

FFast version of bcp 1-17, 2-5Field lengths, copy in 2-29Field terminators, bcp 1-19, 2-16, 2-20 to

2-21File formats, bcp. See Format files (bcp)Files

See also Format files (bcp)backupserver and interfaces 1-4batch 2-33configuration 1-26data transfer format (bcp) 2-2, 2-14,

2-17 to 2-18localization 1-44, 1-45names of defncopy in or out 1-32



native data format 1-9native format in bcp 2-14operating system 2-2 to 2-21operating system, reading with

isql 1-42runserver 1-5, 1-58shared memory and dataserver

command 1-26storage length in bcp 1-20

File storage type (datatype in bcp) 2-17to 2-18

Filters, character set inputisql 1-39

FIPS flagger, isql 1-37Fixed-length fields 2-25Flags, trace 1-5float datatype

bcp and 1-21isql and 1-42

Format files (bcp) 2-21 to 2-24See also Filescopying in with 2-32sample 2-22saving 2-21version number in 2-22

Formatting isql output 3-2

Ggo command terminator (isql) 1-41

HHost files

isql and reading 3-6Human-readable exchange files. See

Character format files (bcp)

IIDENTITY columns

bcp and 1-8, 2-14parallel bcp and 2-12 to 2-14

ignore_dup_key option, create index, andbcp 2-35

image datatype, copying with bcp 1-13,2-18, 2-34

Implicit conversion (of datatypes) 2-17Importing data. See bcp (bulk copy

utility); Copy inin | out option

bcp 1-8defncopy 1-31

Indexesbcp and tables with 1-17, 2-4 to 2-6dropping before using bcp 1-18, 2-5

Information (Server)data transfer 2-3 showserver 1-47

insert commandbcp and 2-3, 2-36compared to bulk copying 2-36permissions 2-3

InstallingAdaptive Server with sybsetup 1-3language using langinstall 1-44 to 1-46messages from previous release 1-45

Installing Adaptive Serversybsetup utility command and 1-3,

1-63Interactive bcp 2-16

See also bcp (bulk copy utility)copying data out 2-25 to 2-29special characters and 1-12

Interfaces fileBackup Server 1-6isql 1-38, 1-48, 1-50, 1-54, 1-56

Invisible terminators (bcp) 2-20iso_1 character set 1-11, 1-39, 2-32isql utility command 1-36 to 1-43, 3-1 to

3-6editors, using in 1-38float datatype and 1-42formatting output 3-2headings 1-38including comments in 1-43maximum statement size 3-1

Index-6


removing line numbers from 1-37removing prompt symbols from 1-37resetting command terminator 1-38specifying options 1-42using interactively 1-41

JJapanese character sets in bcp 1-12

LLANG environment variable 1-5langinstall utility command 1-44 to 1-46Languages, alternate

bcp with 1-13, 2-32defncopy 1-31installing using langinstall 1-44 to 1-46isql 1-40

LC_ALL environment variable 1-5Length of field (bcp) 2-18 to 2-20Line numbers, removing isql 3-6, 3-7load database command, bcp and 2-36load transaction command, bcp and 2-36Localization files 1-44, 1-45Lock state, "sa" account and buildmaster

-m 1-25

Mmaster database

creating with buildmaster 1-23dataserver and 1-26startserver and 1-58

Master device, buildmaster initializationof 1-23

max network packet size configurationparameter 1-38, 2-34

Messages, installing from a previousrelease 1-45

Mirroring master database withdataserver utility command 1-23,1-26

model database, creating withbuildmaster 1-23

money datatype, and bcp copying 1-21Moving data with Adaptive Server

commands 2-36

NNames, defncopy in file or out file 1-32Native data format and bcp 2-21Native data format files, bcp and 1-9Native file format, bcp and 2-14Network connections, backupserver

and 1-5Network packet size

specifying in bcp 1-9specifying in isql 3-6

Newline terminator (\n), bcp 1-19, 2-15,2-20

Non-character datatypes, operatingsystem format for 2-18

Nonprintable characters, host file 2-18Null character terminator, bcp and 2-21Null columns, and bcp copying 1-21Null field terminator (\0), bcp 1-19Null values 2-35

bcp and 2-18Number (quantity of)

go command executions 1-41network connections from the Backup

Server 1-5server connections to the Backup

Server 1-4Numbers

backupserver version 1-5bcp version 1-13controller, for master device 1-23defncopy version 1-31, 1-34dsedit version 1-35line, removing from isql 3-2, 3-6, 3-7line numbering, removing from

isql 1-37release, langinstall and 1-44

Numeric datatypes



operating system format for 2-15

OOpen Server trace flags 1-5Operating system files

bcp copying to or from 1-7 to 1-22reading into isql 1-42

Operating systemscommands prefix (!!) (isql) 1-36, 1-42file format (native format) 2-14, 2-21native format data 1-9non-character datatype,

formatting 2-18numeric datatype formatting 2-15

Output formats, data. See Copy out;Format files (bcp)

Output redirection (isql) 1-42

PPacket size, network

specifying in isql 3-6specifying with bcp 2-34

Padding, data, and bcp copying 1-20,2-18, 2-20

Parallel bcp 2-9 to 2-13copying to a specific partition 2-12different methods of using 2-11 to

2-14-F and -L flags and 2-10, 2-12IDENTITY columns and 2-12 to 2-14partitioned tables and 2-9syntax 2-11

Parser utility. See isql utility commandParsing, data. See Field terminators, bcpPasswords

bcp encryption 1-9buildmaster -m and null 1-25defncopy encryption 1-29forgotten 1-27isql encryption 1-37null, in bcp 1-11null, in isql 1-40

Performancebcp issues 2-4 to 2-14bulk copy and packet size 2-34isql network packet size and 3-6

Permissionsbcp and 2-3changing startserver 1-59defncopy 1-31langinstall 1-45

-P optionlogging in with 1-12passwords and 1-12security and 1-12

Prefix length, bcp field 1-19, 2-18, 2-20Prompts

bcp 1-18, 2-16 to 2-21

QQueries, reading operating system files

with isql 1-42Query buffer, resetting 1-42, 3-3quit command, isql 1-34, 1-36, 3-2Quotation marks (" ") for enclosing

special characters 1-1

R:r (interactive isql) 1-42Read operations, isql and operating

system files 1-42real datatype, and isql 1-42Recovery

bcp speed and 2-4Redirect in symbol (<), isql 1-38, 3-7Redirect out symbol (>), isql 1-39, 3-7Release numbers, and langinstall 1-44reset command, isql 1-36, 3-3Resetting the query buffer 1-42Restarts, Backup Server, and

startserver 1-58Return character. See Carriage return

character (\r)Roll back processes, bcp insert and 2-33

Index-8


Roundingdatatype values in isql 1-42money values in bcp 1-21

Rows, tablebulk copying and failed 2-33, 2-35

Row terminators, bcp 2-16, 2-20 to 2-21Rules

copying data into tables 2-35copying with defncopy 1-29 to 1-33

Runserver file 1-58Backup Server and 1-5format 1-59

SSaving

format files (bcp) 2-21SDF (system data format), and bcp 2-25select command, permissions and 2-3select into/bulkcopy database option, and

bcp 1-17, 2-5select into command, bcp and 2-36server.loc localization file 1-44Server connections (backupserver) 1-4Servers

showserver information 1-47startserver utility command 1-58 to

1-60Server user name and ID, DSQUERY

environment variable 1-13Shared memory file, dataserver 1-26showserver utility command 1-47Single-user mode

dataserver -m option 1-26startserver and 1-58

Sizedata prefix-length (bcp) 1-19data storage (bcp) 1-19master device 1-23packet size 2-34text or image data 2-34

Slow version of bcp 1-17, 2-5sp_dboption system procedure, and

bcp 1-17, 2-5

Space allocationbcp steps and 2-5indexes and triggers, copying with

bcp 1-18Spaces, character 2-18Speed (Server), bcp fast or slow 2-3 to

2-6Spreadsheet programs 2-2SQL parser utility. See isql utility

commandstartserver utility command 1-58 to 1-60

dataserver and 1-27Statistics

isql 1-37, 3-5showserver 1-47

Storage format of data 2-23Storage lengths, bcp file 2-16 to 2-20Stored procedures, copying with

defncopy 1-29 to 1-33sybload utility command

prompts 1-61sybmultbuf executable 1-5sybsetup utility command

configuring Adaptive Server and 1-3,1-63

installing Adaptive Server and 1-3,1-63

SYB storage types 2-23Symbols, field terminator (bcp) 2-20,

2-21Syntax conventions xii to xiiisyslanguages table, langinstall and 1-45sysmessages table, langinstall and 1-45System data format (SDF) and bcp 2-25System Security Officer account 1-27

TTab data field terminator, bcp 1-19, 2-21Table rows. See Rows, tableTables without indexes, bcp and 2-4Tabular data, copying 2-20 to 2-21Tabular output 2-25, 2-28tempdb database



See also Databasestempdb database

buildmaster creation of 1-23Terminator, command. See Command

terminator (isql)Terminators (bcp)

changing 2-16field and row 2-20invisible 2-21specifying 1-12

Text, defncopy copying definitions as 1-32text datatype, copying with bcp 1-13,

2-18, 2-34Timeout option, isql 1-40Trace values 1-5Transaction logs, size 2-4Transact-SQL 3-1Transferring data from spreadsheet

programs 2-2See also bcp (bulk copy utility)

Triggersbcp copying and 1-17, 2-4 to 2-6copying data into tables and 2-35copying with defncopy 1-29 to 1-33dropping before using bcp 1-18

Truncation, data, bcp copying and 1-20

UUnlogged transactions 2-4

VVersion number

backupserver 1-5bcp 1-13bcp, in format file 2-22buildmaster 1-24, 1-27, 1-40defncopy 1-31, 1-34, 1-35langinstall 1-45, 1-48, 1-50, 1-52, 1-53,

1-54, 1-56localization files and langinstall 1-45

vi default editor 1-36, 1-42

Views, copying with defncopy 1-29 to1-33

Index-10
