Monday, January 23, 2012

BCP for Sybase ASE

As I promised to keep my blog green with cool stuff, below is my first posting for 2012.

Scenario: We had a weird problem with Sybase ASE 1503 just for one database where in if we try to dump a database, ASE will throw stack trace. This was weird as out of all the databases on the server, this was the only one having problem. I will call this database as “problem_db”.

Approach: First and the most common approach was to restart backup server which did not helped much and the stack trace was still thrown in the error log.

Secondly, we contacted Sybase and asked for help with this situation and they recommended to delete statistics and recreate them back and we ran dbcc to make sure there were no errors reported. We followed that and still the situation was same.

Until Sybase comes back to us with a proper fix, we decided to bcp out the data and bcp in the data to a new database. Lets call this new database as “healthy_db

Below are the scripts that I used for BCP out and BCP in the data. More info on bcp command can be found here.

Below parameters are quiet useful for BCPing the data into the database:

Parameter Explanation
-n performs the copy operation using native (operating system) formats. Specifying the -n parameter means bcp will not prompt for each field. Files in native data format are not human-readable.
-Y specifies that character-set conversion is disabled in the server, and is instead performed by bcp on the client side when using bcp IN.
-E bcp reads the Identity value from the data file and sends it to the server which inserts the value into the table.
-T allows you to specify, in bytes, the maximum length of text or image data that Adaptive Server sends. The default is 32K. If a text or an image field is larger than the value of -T or the default, bcp does not send the overflow.

You can find this value by doing: SELECT @@TEXTSIZE
-e This is the error log file created by bcp and it will log only the first 10 rows that caused the errors as the default for bcp is 10 errors and after that bcp will fail.

Note that ASE was running 1503 on a Solaris platform.

-- BCP OUT SCRIPT


select 'bcp problem_db.dbo.' + name + ' out /bcp_out_files/' + name + '.out -SSERVER_NAME -Usa -PSTRONG_PASSWORD -n'
from sysobjects
where type='U'
order by name

Now for the bcp in script, there are two different scripts used for Tables having Identity columns and the ones which did not have Identity columns:

-- BCP IN SCRIPT

--  (this will be for tables not having identity columns)
select 'LANG=C' + char(10)+'bcp healthy_db.dbo.' + so.name + ' in /bcp_out_files/' + so.name + '.out -SSERVER_NAME -Usa -PSTRONG_PASSWORD -n -Y -T 64512 -e /bcp/bcp_error.out' + char(10) +
'echo '+ so.name +'  done ...' from sysobjects so where so.name not in (
select so.name
from sysobjects so, syscolumns sc
where so.id = sc.id
and sc.status & 0x80 = 0x80)
and so.type = 'U'
order by so.name

--  (this will be for tables having identity columns, –E parameter is used for bcp)
select 'LANG=C' + char(10)+'bcp healthy_db.dbo.' + so.name + ' in /bcp_out_files/' + so.name + '.out -SSERVER_NAME -Usa -PSTRONG_PASSWORD -n -Y -E -T 64512 -e /bcp/bcp_error.out' + char(10) +
'echo '+ so.name +'  done ...'
from sysobjects so, syscolumns sc
where so.id = sc.id
and sc.status & 0x80 = 0x80
and so.type = 'U'
order by so.name

The above scripts will generate code for bcp out and in to run using isql (command prompt) on Solaris server.

Also, the schema and other dependent objects were created before hand.

HTH,

\\K

Saturday, January 21, 2012

Back to writing blog …

I have been very busy with job duties as now learning Sybase ASE and taking on new responsibilities as SQL server and Sybase DBA.

I was not able to allocate time to keep my blog alive for almost a year.

This is my first post in 2012. And want to keep this as habit of regularly updating my blog with both MS SQL Server and Sybase.

Hope you will enjoy reading new cool stuff on this blog.

Many thanks,

SQL_Quest

This blog is for SQL server and ArcSDE administration. Thanks for visiting !

Disclaimer: The contents provided on this blog are as is and the poster assumes no responsibility in case of any loss of data. Please test thoroughly the solution or codes provided in your environment.