Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Migration from Sybase ASA to ASE 12.0

4 posts in Migration Last posting was on 2001-05-25 12:06:28.0Z
Mahesh Posted on 2001-05-18 23:24:24.0Z
From: Mahesh
Date: Fri, 18 May 2001 19:24:24 -0400
Newsgroups: sybase.public.sqlserver.migration
Subject: Migration from Sybase ASA to ASE 12.0
Message-ID: <6AEEEA8D27BB0BE1008093EF85256A50.0080940085256A50@webforums>
Lines: 39
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub sybase.public.sqlserver.migration:219
Article PK: 1067643

Hi,
I have a couple of questions when I am doing a migration from ASA SQL
Anywhere 6.0 to Sybase ASE 12.0
I am planning to unload ASA data into data files and then bcp them into
ASE.

#1. I wish to bcp binary data from ASA. But the problem here is that the
hex data in the binary columns are represented as \xAA\xAB\x2A i.e., \x
precedes every hex data whereas the corresponding column in ASE (image
datatype in ASE) requires the same data as 0xAAAB2A. So, is there a way to
get the data out of ASA in the same format! I beleive there is an option
called HEXADECIMAL ON/OFF for the same but that DID NOT work on ASA 6.0.

#2. This is a very peculiar problem. I wanted to bcp some varchar data as
well. So I unload ASA table with quotes off. These varchar data can contain
both NULL value and an empty string "". When I unload the table both the
null value as well as the empty string is unloaded as x,,y(where the table
has 3 columns (int x,varchar(10),int y) and , is the delimiter). So when I
bcp it into ASE, it is inserted as NULL for that column whereas that column
could either have been a NULL or an empty string. I have worked around it
by unloading the ASA table with the quotes on, so empty string comes in
quotes and NULL is as above. Then I insert a space wherever there is an
empty quotes and then remove the quotes and then bcp. This works but there
should be some other way out as this is a brute force work-around and can
actually take a long time when I am trying to unload a HUGE database.
I would be glad if anybody can give me a neat alternative. Also if I unload
with quotes on and then bcp the data the quotes also goes in as data as
bulk copy is just blind copy. So is there any argument to bcp that makes it
assume varchar/strings to be enclosed in quotes as in ASA (QUOTES ON/OFF)

#3. What is the correct mapping of text datatype from ASA to ASE?
Are there any known issues with the text datatype of ASE..I mean will the
application using the DB have any problem now (will it have to be
changed??) if it were earlier using ASA text and now if we are using ASE
text? Another question is is it okay to convert text ti image datatype in
ASE???

I know I have asked a lot of questions...
It would be great if I could a few answers from here.
Thanks and Regards
Mahesh


Anthony Mandic <amandic Posted on 2001-05-23 08:49:15.0Z
Date: Wed, 23 May 2001 18:49:15 +1000
From: Anthony Mandic <amandic@*t.com.au>
Organization: Mandic Consulting Pty. Ltd.
X-Mailer: Mozilla 4.61 [en] (WinNT; I)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: Migration from Sybase ASA to ASE 12.0
References: <6AEEEA8D27BB0BE1008093EF85256A50.0080940085256A50@webforums>
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
Message-ID: <o1yEbh24AHA.241@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.migration
Lines: 88
NNTP-Posting-Host: CPE-208-111-111-203.syd.dav.net.au 203.111.111.208
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.migration:216
Article PK: 1067641


Mahesh wrote:

> #1. I wish to bcp binary data from ASA. But the problem here is that the
> hex data in the binary columns are represented as \xAA\xAB\x2A i.e., \x
> precedes every hex data whereas the corresponding column in ASE (image
> datatype in ASE) requires the same data as 0xAAAB2A. So, is there a way to
> get the data out of ASA in the same format! I beleive there is an option
> called HEXADECIMAL ON/OFF for the same but that DID NOT work on ASA 6.0.

With the correct tools, this can easily be correctly. But my guess
is that you are on a PC or NT platform so said tools are less
forthcoming. On Unix you would use sed to convert the first \x
to 0x and then strip off every other instance of \x. There are
Unix-like tools available for NT. Look around for the MKS Toolkit
or Cygwin. What you need is something like sed (a streaming
editor) or awk or you could even use Perl to do this.

> #2. This is a very peculiar problem. I wanted to bcp some varchar data as
> well. So I unload ASA table with quotes off. These varchar data can contain
> both NULL value and an empty string "". When I unload the table both the
> null value as well as the empty string is unloaded as x,,y(where the table
> has 3 columns (int x,varchar(10),int y) and , is the delimiter). So when I
> bcp it into ASE, it is inserted as NULL for that column whereas that column
> could either have been a NULL or an empty string. I have worked around it
> by unloading the ASA table with the quotes on, so empty string comes in
> quotes and NULL is as above. Then I insert a space wherever there is an
> empty quotes and then remove the quotes and then bcp. This works but there
> should be some other way out as this is a brute force work-around and can
> actually take a long time when I am trying to unload a HUGE database.
> I would be glad if anybody can give me a neat alternative. Also if I unload
> with quotes on and then bcp the data the quotes also goes in as data as
> bulk copy is just blind copy. So is there any argument to bcp that makes it
> assume varchar/strings to be enclosed in quotes as in ASA (QUOTES ON/OFF)

You can use a format table with bcp. But handling the quotes is
still ugly since its geared towards column separators rather than
terminators (i.e. you'll still have a problem with the start and
end of each row line). However, I'm not sure I'm with you when
you say your change works with ASE. ASE treats the empty string
as a null string when using varchar - their is no real distinction.
Does your application need this distinction?

> #3. What is the correct mapping of text datatype from ASA to ASE?
> Are there any known issues with the text datatype of ASE..I mean will the
> application using the DB have any problem now (will it have to be
> changed??) if it were earlier using ASA text and now if we are using ASE
> text? Another question is is it okay to convert text ti image datatype in
> ASE???

Text handling is fairly straightforward but bare bones within ASE.
About the only thing you can really do is select and insert. You
can only update text by doing it on the client and then deleting
and reinserting on the server. Pattern match searches are a real
pain too. If you are doing something more sophisticated, you will
need to review it. This applies to image datatypes too. Java datatype
handling may be a better approach - if you are using the Java VM in
the server.

-am © 2001


Maesh Posted on 2001-05-23 16:55:15.0Z
From: Maesh
Date: Wed, 23 May 2001 12:55:15 -0400
Newsgroups: sybase.public.sqlserver.migration
Subject: Re: Migration from Sybase ASA to ASE 12.0
Message-ID: <CA2A5DEB632417B5005CF2B785256A55.0035378485256A55@webforums>
References: <6AEEEA8D27BB0BE1008093EF85256A50.0080940085256A50@webforums> <o1yEbh24AHA.241@forums.sybase.com>
Lines: 27
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub sybase.public.sqlserver.migration:215
Article PK: 1067639

Hello,
Well, about the first part regarding the binary data bcp, I have done the
same thru' an application whcih would remove the \x and replace them... as
"sed", unfortunately does not work on Win!
What I wanted to know was why at all have such a workaround..is there any
option provided by Sybase themselves which would do it?
About the #2 question regarding varchars, well when a line like the
following (all fields are varchars)
x,,z is bcped into ase it will be inserted as x,NULL,z whereas if x, ,z is
bcped it will be inserted as x,"",z!!!
So if an empty string is to be bcped, we need to have atleast one space
between the field delimiters!For NULL, dont have any delimiters! To
incorporate this logic, I needed to put the quotes on,etc! Is there any
documented workaround this issue.
The application distinguishes between empty string and NULL, as our ASE
table schema does not allow NULLS! And ASA had empty strings for many
varchar columns!
More importantly and thirdly, we are having a issue with text datatype. We
converted the text datatype to image in ASE expecting both be a varbinary!
It so happens that ASA converts the printable characters in the text
datatype to printable format and the non-printable formats are left as it
is in binary! So when I unload the text datatype has both printable
characters and hex data! I am not able to bcp this into ASE as ASE image
cannot allow non hex data!!! Any pointers as to how to overcome this
problem?
Thanks for your effort
Regards
Mahesh


Anthony Mandic <amandic Posted on 2001-05-25 12:06:28.0Z
Date: Fri, 25 May 2001 22:06:28 +1000
From: Anthony Mandic <amandic@*t.com.au>
Organization: Mandic Consulting Pty. Ltd.
X-Mailer: Mozilla 4.61 [en] (WinNT; I)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: Migration from Sybase ASA to ASE 12.0
References: <6AEEEA8D27BB0BE1008093EF85256A50.0080940085256A50@webforums> <o1yEbh24AHA.241@forums.sybase.com> <CA2A5DEB632417B5005CF2B785256A55.0035378485256A55@webforums>
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
Message-ID: <Nux3$YR5AHA.249@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.migration
Lines: 74
NNTP-Posting-Host: CPE-208-111-111-203.syd.dav.net.au 203.111.111.208
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.migration:214
Article PK: 1067637


Maesh wrote:

> Well, about the first part regarding the binary data bcp, I have done the
> same thru' an application whcih would remove the \x and replace them... as
> "sed", unfortunately does not work on Win!
> What I wanted to know was why at all have such a workaround..is there any
> option provided by Sybase themselves which would do it?

Well, ASE's bcp doesn't. I'm not familiar with ASA (you try in one
of the ASA related newsgroups), so I can't say whethere there's
another way of extracting it. Other options might be to use
SQL Remote or Sybase Replication Server to copy from server to
server.

> About the #2 question regarding varchars, well when a line like the
> following (all fields are varchars)
> x,,z is bcped into ase it will be inserted as x,NULL,z whereas if x, ,z is
> bcped it will be inserted as x,"",z!!!

OK, I'm following you now.

> So if an empty string is to be bcped, we need to have atleast one space
> between the field delimiters!

To be strictly correct, that should be field separators.

> For NULL, dont have any delimiters! To
> incorporate this logic, I needed to put the quotes on,etc! Is there any
> documented workaround this issue.

Ah, you should have something more like -

x<separator>y<separator>z

where <separator> is the one you chose. This is a tab by default
in bcp. So you could have either -

x<separator> <separtor>z
or
x<separator><separator>z

to distinguish between a space and a null.

> The application distinguishes between empty string and NULL, as our ASE
> table schema does not allow NULLS! And ASA had empty strings for many
> varchar columns!

Hmmm ... you shouldn't have any nulls in the data then. I'm not
sure what bcp would do when you bcp in a null value. Check what
it does in a test.

> More importantly and thirdly, we are having a issue with text datatype. We
> converted the text datatype to image in ASE expecting both be a varbinary!

varbinary is a different datatype to text and image. Please be
cautious here and check the differences in the docs.

> It so happens that ASA converts the printable characters in the text
> datatype to printable format and the non-printable formats are left as it
> is in binary! So when I unload the text datatype has both printable
> characters and hex data! I am not able to bcp this into ASE as ASE image
> cannot allow non hex data!!! Any pointers as to how to overcome this
> problem?

That's going to be difficult. bcp will load it as is without
interpretation. So your end result will look like the intermediate
file rather than the original data. It increasing looks like
SQL Remote might be a better bet - do a server to server copy
and avoid all the munging done via the ASA extraction program.

I'd suggest chasing up in the ASA newsgroups about these
issues and how to use SQL Remote.

-am © 2001