Switch to standard view 
  Sybase logo
 
 
 



Contents

The BCP_IN sample script's command lines and routines are intended for you to use as a model for creating a script that is easy to maintain and troubleshoot..

Edit Considerations

In tailoring the sample script to your needs, consider these characteristics of the script:

Note
Sybase Technical Support does not support this script.

Debugging Tip

Use the C-shell option "-xvf" instead of "-f" in the line at the top of bcp files to aid in debugging. For example, "#!/bin/csh -xvf".

Editing Environment Variables

Once you have copied or created the script, edit the environment variables to reflect your current operating configuration.

To find the section to edit, search on the string ENVIRONMENTAL VARIABLE SECTION. Each variable is commented to aid you in editing. The following topics provide additional edit information.

Usage Switches

A usage statement is returned if you invoke BCP_IN without any switches. The usage switches are:

BCPFILEDIR Variable

Be sure to point the BCPFILEDIR environment variable to a directory that has enough free space to store the bulk copy data.

Confirmation Options

Two confirmation options are available:

Note
Changing CONF_SELECT and BCP_OVERWRITE to 1 may cause unexpected results in cron jobs.

bcp Command Settings

To change the column or row terminators or any other bcp command setting, edit the bcp command line. Search for the string "$BCP $DBNAME.." .

cron Submission

BCP_IN allows for submission of bcp commands via the UNIX cron command.

For example, you could schedule bcp of the pubs2 database for every Sunday, one minute after midnight.

The actual cron command file syntax depends on your UNIX environment.

Run a Test First!

Once you have edited the sample script to reflect your current environment, we recommend that you test and fine-tune it before using it in a production environment.

Example Scenario

For example, you could create a test server with two or three small databases where each of the databases contains a few tables with minimal data. Let us say that you have a 300 MB database called testdb where:

  1. Change the file permissions mode to executable: 
    chmod +x bcp_in 

    chmod +x bcp_out
  2. Bulk copy out the data using the single database switch: 
    bcp_out -s testdb
    For details, see the companion TechNote, "Using the BCP_OUT Sample Script to Create Tables."

  3. Invoke isql and perform the following tasks: >> isql -Usa -P

    1> use master 2> go

    1> sp_dboption "testdb","select into","true" 2> go

    1> use testdb 2> go

    1> checkpoint 2> go

    1> quit

    These tasks do as follows:

  1. Bulk copy in the data, using the -s switch: 
    bcp_in -s testdb
  2. Dump the database. You can optionally turn off the select into/bulkcopy option.

    After completing the steps in this example, you are ready to perform subsequent backups/recovery.

BCP_IN Sample Script 

# -----------------------------------------------------
# ENVIRONMENTAL VARIABLE SECTION
# -----------------------------------------------------

# Specify where to place the bcp files.

setenv BCPFILEDIR "/tmp"

# SYBASE and DSQUERY reflect the operating environment 
# for your server.

setenv SYBASE "/sybase"
setenv DSQUERY "my_servername"

# PASSWD most likely needs to reflect the sa.

setenv PASSWD "-Usa -P "

# Set BCP to the path for the bcp executable, which
# inherits the $SYBASE variable. 

setenv BCP "$SYBASE/bin/bcp"

# ISQL reflects a valid path for both the isql 
# executable and a valid command string.

setenv ISQL "$SYBASE/bin/isql $PASSWD -S$DSQUERY"

# Set the batch size for bcp in.

set BATCHER=100

# If you want a prompt to confirm your database 
# choices, set the CONF_SELECT flag to 1.

setenv CONF_SELECT "0"

# If you want a prompt when bcp outfiles already exist 
# for the database/table qualifiers chosen, set the 
# BCP_OVERWRITE flag to 1. If the flag is set to 
# "0"(default), existing bcp files will be overwritten.

setenv BCP_OVERWRITE "0"

# Do not modify the umask setting.

umask 0

# -----------------------------------------------------
# SELECT DATABASE PROCESSING SECTION
# -------------------------------------------------

# Set SELECT_DB to "1" for automatic lookup of values
# in sel_dblist. Otherwise, use the default "0".

setenv SELECT_DB "0"

Reviewers: I recommend deleting this: # You can type in the names of databases to bcp out by placing # the names between the quotes of sel_dblist. # Make sure there are spaces between databases. This is # only activated if select_db = 1 (above), and may not work # properly anyway. On second thought, don't fool with this. setenv SEL_DBLIST "" 

# Do not modify the counter variables NO_TABLES FOUND
# and NOARGS.

set NO_TABLES_FOUND = 0
set NOARGS=1

if (-e /tmp/db_list) rm /tmp/db_list
if (-e /tmp/db_list1) rm /tmp/db_list1

if (-e /tmp/table_list) rm /tmp/table_list
if (-e /tmp/table_list1) rm /tmp/table_list1

if (-e /tmp/tmp_bcpdbs) rm /tmp/tmp_bcpdbs

# -----------------------------------------------------
# STARTING MENU
# -------------------------------------------------

echo ""
echo " ________________________________________"
echo "|                                        |"
echo "| >>> SYBASE BCPFILE INPUT AUTOMATOR <<< |"
echo "|________________________________________|"
echo ""

if ($#argv == 0) then
	set SW = "0"
else
	set SW = "\Qecho $argv[1] | cut -d"-" -f2\Q"
endif

if ($SW != "a" && $SW != "s" && $SW != "d" && $SW != 
"t") then
	echo "Usage: "
	echo "------"
	echo "     bcp_in [-a]"
	echo "            [-s]"
	echo "            [-s] <dbname1> <dbnameX> ... "
	echo "            [-t]"
	echo "            [-d]"
	echo "where"
	echo ""
	echo " [a] bcp in all qualifying bcpfiles "
	echo " [-s] bcp in select databases from user input"
	echo " [-s <db1> <dbX> ...] bcp in listed databases"
	echo " [-t] bcp in one file for a specific table"
	echo " [-d] echos variables in use"
	echo ""
	exit

else if ($SW == "s" && "$#argv" >= "2") then
	set RCT = "1"
	while ($#argv > $RCT )
		set SELECT_DB = 1
		set VARCT=\Qexpr $RCT + 1\Q
		set SEL_DBLIST=($SEL_DBLIST $argv[$VARCT])
		set RCT = \Qexpr $RCT + 1\Q
	end

else if ($SW == "s" || $SW == "t") then
		set SELECT_DB = 1

else if ($SW == "d") then
		set YCK = 1

		if (! $?SYBASE) then
			set SYBASE = "<Value not set - edit file>"
			set YCK = 0
		endif

		if (! $?DSQUERY) then
			set DSQUERY = "<Value not set - edit file>"
			set YCK = 0
		endif

		if (! $?ISQL) then
			set ISQL = "<Value not set - edit file>"
			set YCK = 0
		endif

		if (! $?PASSWORD) then
			set PASSWORD = "<Value not set - edit file>"
			set YCK = 0
		endif

		if (! $?BCPFILEDIR) then
			set BCP DIRECTORY = "<Value not set - edit 
			file>"
			set YCK = 0
		endif

		echo " Variable List"
		echo "------------------------------"
		echo ""
		echo "SYBASE        = $SYBASE"
		echo "DSQUERY       = $DSQUERY"
		echo "ISQL          = $ISQL"
		echo "PASSWORD      = $PASSWD"
		echo "BCP DIRECTORY = $BCPFILEDIR"
		echo ""
		exit
endif

# Confirm database choice.

if ($SELECT_DB == 1 && $SW != "t") then
	echo ""
	echo "Selective Database Option Enabled."
	echo ""

	if ($SW == "s" && $#argv < 2) then
		echo "Enter Database Names (one at a time)"
		echo "and terminate list with a <CR>."
		echo ""

		set ct=1
		set CHKIT=1
		set TWOSTRIKES = 1

		if (-e /tmp/qualify) rm /tmp/qualify
		while ($ct == 1) 
			echo -n "DBNAME >> "
			set QUALIFY=($<)
			ls ${BCPFILEDIR}/${QUALIFY}* 2>&1
			if ($status != 0) then
				echo ""
				echo "*** Warning: No files found for"
				echo "   that Database. Please try again."
				echo ""
				set CHKIT=0
				set TWOSTRIKES = 0

			else if ("$QUALIFY" == "" && $CHKIT == 1) then
				echo ""
				echo "*** Warning : please input a valid"
				echo "    Database name <or x to exit>."
				echo ""
				set CHKIT=0
				set TWOSTRIKES = 0

			else if ("$QUALIFY" == "x") then
				echo ""
				echo "... Program exiting ..."
				echo ""
				exit	

			else if ("$QUALIFY" == "") then
				if ($CHKIT == 0 && $TWOSTRIKES == 0) then
					echo ""
					echo "... Program exiting ..."
					echo ""
					exit
				endif
				set ct=0
			else
				echo $QUALIFY >> /tmp/qualify
				set CHKIT=0
			endif
		end

			set SEL_DBLIST=\Qcat /tmp/qualify\Q
	endif
	echo ""

else if ($SW == "t") then
	echo ""
	echo "Single Table Option Enabled"
	echo ""
	set SELECT_DB=1
	echo -n "ENTER DBNAME    >>"
	set QUALDB=($<)
	set SEL_DBLIST="${QUALDB}"
	echo -n "ENTER TABLENAME >>"
	set QUALTBL=($<)
	echo ""
	if (! -e 	${BCPFILEDIR}/${QUALDB}.${QUALTBL}.bcpfile) 
	then
		echo ""
		echo "*** Warning: No bcp files found for"
		echo "    that table. Please try again."
		echo ""
		ls ${BCPFILEDIR}
		echo ""
		echo "... program exiting ..."
		echo ""
		exit
	endif
	echo ${QUALDB} > /tmp/db_list

else if ($SELECT_DB != 1 ) then
	echo ""
	echo "All User Databases Option Enabled."
	echo ""

	ls ${BCPFILEDIR}/*.bcpfile 2>&1
	if ($status != 0) then
		echo ""
		echo "*** Warning: No qualifying files found for"
		echo "  any Database. Please confirm choices
		echo "  and invoke script again."
		echo ""
		echo "... program exiting ..."
		echo ""
		exit
	endif

	if (-e /tmp/bcpdbs) rm /tmp/bcpdbs
	touch /tmp/tmp_bcpdbs

	foreach file (\Qls ${BCPFILEDIR}/*bcpfile\Q)
		echo $file:t | cut -d. -f1 >> /tmp/bcpdbs
	end

	uniq /tmp/bcpdbs > /tmp/db_list

endif

# Obtain select database list, if enabled.

if ($SELECT_DB == "1") then

	foreach MANUAL_DB (\Qecho $SEL_DBLIST\Q)
	if (-e /tmp/sdb_list) rm /tmp/sdb_list

$ISQL $PASSWD << ENDCMDS > /tmp/sdb_list

if not exists(select name from master..sysdatabases 
where name = "$MANUAL_DB") print "DBNOTFOUND"

go

ENDCMDS

	grep "DBNOTFOUND" /tmp/sdb_list > /dev/null
	if ($status == "0") then
		echo ""
		echo "*** Warning.. Incorrect Database Name. ***"
		echo ""
		echo "DATABASE :  $MANUAL_DB"
		echo ""
		echo "Processing of BCP files terminated. Please"
		echo "confirm proper spelling and invoke script"
		echo "again."
		echo ""
		echo "... Program Exiting ..."
		echo ""
		exit
	endif

# Close out database selection list verification.

	end

# Restore the selective database list.

	if (-e /tmp/db_list1) rm /tmp/db_list1
	touch /tmp/db_list1
	foreach SELDBNAME (\Qecho $SEL_DBLIST\Q)
		echo  $SELDBNAME >> /tmp/db_list1
	end

# Otherwise, validate the entire database list.

else
	set TMPCHK=0
foreach DB_FILE_NAMED (\Qcat /tmp/db_list\Q)

$ISQL $PASSWD << ENDCMDS >/tmp/db_list1

if not exists (select name from master..sysdatabases 
where name= "${DB_FILE_NAMED}") print "DBDOESNOTEXIST"

go

ENDCMDS

	grep "DBDOESNOTEXIST" /tmp/db_list1 > /dev/null
	if ($status == 0) then
		echo ""
		echo "*** Warning.. Database Does Not Exist. ***"
		echo ""
		echo "DATABASE :  $DB_FILE_NAMED"
		echo ""
		echo "... continuing with next database ..."
		echo ""
		set TMPCHK=1
	else
			set TMPCHK=0
	endif
end
if (${TMPCHK} == 1) then
		echo "*** Warning.. No Databases Qualify ***"
		echo ""
		echo "Processing of BCP files terminated. Please"
		echo "confirm proper spelling and invoke script"
		echo "again."
		echo ""
		echo "... Program Exiting ..."
		echo ""
		exit
	endif

cp /tmp/db_list /tmp/db_list1

# Complete the database selection processing.

endif

	echo "-----------------------------------------"
	echo "The following Database list qualifies for"
	echo "bcp operations:"
	echo "-----------------------------------------"
	cat /tmp/db_list1
	echo ""

if ($CONF_SELECT == 1) then
	echo -n "*** Press "y" to continue >>>"
	set p_cont=($<)
	if ($p_cont != "y" && $p_cont != "Y") then
		echo ""
		echo "... Program exiting ..."
		echo ""
		exit
	endif
endif

set TMPCHK=0

foreach DBNAME (\Qcat /tmp/db_list1\Q)
echo ""
echo "================================="
echo ">>>>> DATABASE: $DBNAME "
echo "================================="
echo ""
echo "... confirming bcp table files for $DBNAME..."

# Get bcp files and confirm tables in each database.
# First, obtain bcp files.

if (-e /tmp/bcptbl1) rm /tmp/bcptbl1
if (-e /tmp/tbl_list2) rm /tmp/tbl_list2

	if ($SW != "t") then
	foreach BCPFQUAL 
		(\Qls ${BCPFILEDIR}/${DBNAME}*bcpfile\Q)
	else 
	foreach BCPFQUAL 
		(\Qls ${BCPFILEDIR}/${DBNAME}.${QUALTBL}.bcpfile\Q)
	endif

	set BT=\Qecho ${BCPFQUAL}:t | cut -d. -f2 
		| cut -d. -f1\Q

	echo ""
	echo "---------------------------------"
	echo " * BCP file input for : $BCPFQUAL"
	echo "-------------------------------"

$ISQL $PASSWD << ENDCMDS >/tmp/tbl_list2

if not exists (select name from ${DBNAME}..sysobjects 
where name="${BT}") print "TABLEDOESNOTEXIST"

go

ENDCMDS

	grep "TABLEDOESNOTEXIST" /tmp/tbl_list2 > /dev/null
	if ($status == 0) then

		echo ""
		echo "*** Warning.. Table Does Not Exist. ***"
		echo ""
		echo "DATABASE :  $DBNAME"
		echo "TABLE    :  $BT"
		echo ""
		echo "... continuing with next table ... "
		echo ""
		set TMPCHK=1
	else
			set TMPCHK=0
	endif

$BCP $DBNAME..$BT in $BCPFQUAL $PASSWD  -b $BATCHER -c 

# Go to the next table.

	end

	echo ""
	echo "==================================="
	echo ">>>>> BCP file input processing for"
	echo ">>>>> $DBNAME completed."
	echo "==================================="
	echo ""

# Continue processing if no tables were found.

endif

# Go to the next database.

end

# Clean up.
#if (-e /tmp/db_list) rm /tmp/db_list
#if (-e /tmp/db_list1) rm /tmp/db_list1
#if (-e /tmp/table_list) rm /tmp/table_list
#if (-e /tmp/table_list1) rm /tmp/table_list1

Should "#" precede the above "if" statements?

echo ""
echo "... Program Exiting ..."
echo ""


Back to Top
Home / Privacy / Contact / Terms of Use
© Copyright 2010, Sybase Inc.