I got two questions

Discussion:

I got two questions

(too old to reply)

Taylor

2009-10-13 11:52:18 UTC

I got two questions. Thanks in advance.

1. Is there any SQL that can list the tables'name and size in a
database? It's impossible to use sp_spaceused on each table since
there are two many tables.

2. I used ASE12.5.3 bcp out a table's data and tried to use
ASE15.0.3's bcp to bcp into ASE15.0.3 table and got following error.
Starting copy...
CSLIB Message: - L0/O0/S0/N24/1/0:
cs_convert: cslib user api layer: common library error: The conversion/
operation was stopped due to a syntax error in the source field.
CSLIB Message: - L0/O0/S0/N36/1/0:
cs_convert: cslib user api layer: common library error: The result is
truncated because the conversion/operation resulted in overflow.
CSLIB Message: - L0/O0/S0/N24/1/0:

But I used a tool to convert the data in the table to insert sql
statements and it runned successfully on the target database. What's
the reason of this bcp failure? Any difference between the two
versions?
Thanks again for your advice.

Carl Kayser

2009-10-13 12:45:46 UTC

Permalink

Sure. There is plenty of home-made code around that does this (e.g.,
sp_helptable at http://www.edbarlow.com/gem/procs_only/index.htm). But
you're going to have to specify the ASE version since a number of builtin
functions changed with 15.0.3. (There is the question of reserved space
versus used space, etc.)

Post by Taylor
2. I used ASE12.5.3 bcp out a table's data and tried to use
ASE15.0.3's bcp to bcp into ASE15.0.3 table and got following error.
Starting copy...
cs_convert: cslib user api layer: common library error: The conversion/
operation was stopped due to a syntax error in the source field.
cs_convert: cslib user api layer: common library error: The result is
truncated because the conversion/operation resulted in overflow.

No help on this from me.

Post by Taylor
But I used a tool to convert the data in the table to insert sql
statements and it runned successfully on the target database. What's
the reason of this bcp failure? Any difference between the two
versions?
Thanks again for your advice.

Carl Kayser

2009-10-13 12:47:51 UTC

Permalink

Post by Carl Kayser
Sure. There is plenty of home-made code around that does this (e.g.,
sp_helptable at http://www.edbarlow.com/gem/procs_only/index.htm). But
you're going to have to specify the ASE version since a number of builtin
functions changed with 15.0.3. (There is the question of reserved space
versus used space, etc.)

Should be 15.0 and not 15.0.3.

Neal Stack [Sybase]

2009-10-13 14:08:55 UTC

Permalink

Hello,

Regarding the bcp, what syntax did you use during the bcp in/out operations?

Since you used different versions of bcp, I assume you ftp'd the data from
one machine to another. Did you ftp in ascii or binary format?

If these two versions of ASE are in the same network, why not just bcp out
and in with the same version of bcp? Skip the ftp and save yourself a step.

Regards,
Neal

Taylor

2009-10-19 08:16:40 UTC

Permalink

Post by Neal Stack [Sybase]
Hello,
Regarding the bcp, what syntax did you use during the bcp in/out operations?
Since you used different versions of bcp, I assume you ftp'd the data from
one machine to another. Did you ftp in ascii or binary format?
If these two versions of ASE are in the same network, why not just bcp out
and in with the same version of bcp? Skip the ftp and save yourself a step.
Regards,
Neal

That's correct. I bcp out and ftp to the destination server. I forgot
which mode I used. But I don't think it is the reason of FTP mode.
Since 200 tables are ftped and most of them succeeded in bcp in but
nearly 10 of them failed.
bcp command: bcp db_name..table_name out/in outputfile.bcp -c -U -S -P

Sherlock, Kevin [TeamSybase]

2009-10-19 17:34:23 UTC

Permalink

bcp command: bcp db_name..table_name out/in outputfile.bcp -c -U -S -P

Given your bcp command above, you rely on the default field separator of a
"tab" character, and record separator of a "newline". The question then
becomes, do you have any tabs, or newlines in your source data? If so, then
you need to choose different separators for either "-t" option or "-r"
option.

unknown

2009-10-13 15:12:48 UTC

Permalink

First things first:
"I've got two questions", "and there are too many tables."
-- there, I had to get that out of the way.

To construct your own SQL code, simply look at the source
code for "sp_spaceused" system stored procedure by looking
at either $SYBASE/$SYBASE_ASE/scripts/installmaster, or by
using sp_helptext to list the procedure contents. Find out
how that procedure derives the necessary information, then
write your own code.

bcp - you should show us the exact bcp commands you used and
tell us if you did anything with the data file between
commands (such as ftp, or editing, etc). Also, make sure
the table definitions are exactly the same between the two
databases.

Sherlock, Kevin [TeamSybase]

2009-10-13 15:15:24 UTC

Permalink

1. Just find the code for "sp_spaceused" and create your own procedure or
script that uses the same logic and format the output the way you want it.
2. what options did you use for your bcp commands? Can you be more
specific about how exactly you created the new table, what options you used
for bcp, and if you ftp'd the file to a different server?

Post by Taylor
I got two questions. Thanks in advance.
1. Is there any SQL that can list the tables'name and size in a
database? It's impossible to use sp_spaceused on each table since
there are two many tables.
2. I used ASE12.5.3 bcp out a table's data and tried to use
ASE15.0.3's bcp to bcp into ASE15.0.3 table and got following error.
Starting copy...
cs_convert: cslib user api layer: common library error: The conversion/
operation was stopped due to a syntax error in the source field.
cs_convert: cslib user api layer: common library error: The result is
truncated because the conversion/operation resulted in overflow.
But I used a tool to convert the data in the table to insert sql
statements and it runned successfully on the target database. What's
the reason of this bcp failure? Any difference between the two
versions?
Thanks again for your advice.

Rob V [ Sybase ]

2009-10-13 16:45:29 UTC

Permalink

As for question #1: you can run a query against the system tables using
built-ins like data_pgs() (in 12.5) or data_pages() (in 15); you may need to
run a group-by query summing the sizes for all indexes and the data, if you
want the total size for all.

An even easier way is to install the stored procs at
www.sypron.nl/new_ssp.html.
Then run the following to get the size of all tables in database 'XYZ':

sp_rv_findobject 'db=XYZ'display=NM SZ', 'type=U'

HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
and Replication Server 15.0.1/12.5 // TeamSybase

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE" (ASE 15 edition)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

mailto:***@YOUR.SPAM.sypron.nl.NOT.FOR.ME
http://www.sypron.nl
Sypron B.V., Amersfoort, The Netherlands
Chamber of Commerce 27138666
-----------------------------------------------------------------