Sybase ASE 11.2 to ASE 12.5 Developer

[freddydoesphp]

I am very new to Sybase ASE, so amy responses will be appreciated.

I have to transfer a Syabse ASE 11.2 database to 12.5 developer edition. I do not have access to the physical machine with the database, I have remote access, through Sybase Central and Infomaker. I have managed to get the database objects created by generating a DDL script. How can get the data from the remote database to my new database server running locally.

Will I have any problems with compatibility between the two versions?

Thanks in advance.

[MattR]

You do not need to use the DDL or BCP out.

Simply dump the database to a file then load it into the new system. It will upgrade the tables in-place. You will have to drop and rebuild compiled objects (stored procedures, triggers, etc.).

[freddydoesphp]

Thanks MattR, I knew it would be you who answered oh Sybase God. But to sound even dumber, how do I dump the database to a file, if I only have remote access to the database through InfoMaker, or Open CLient. Or Sybase Central. Thanks for the answers.

[MattR]

You don't have access to *any* files on the DB machine? Do you have a machine login?

That seems silly if you don't!

If you do have filesystem access:
DUMP DATABASE your_db TO "/what/ever/path/file"

If it is over 2GB and your filesystem does not support individual files larger than 2GB you will need to run:
DUMP DATABASE your_db TO "/file1" STRIPE ON "/file2" STRIPE ON "/file3" etc.

Then simply download the files.

You cannot do it directly if you don’t have access to the filesystem. Can you edit the interfaces file? If so:

Edit (you or the sysadmin) the interfaces file with an entry like:
[REMOTE_BACKUP_SERVER]
REMOTE_BACKUP_SERVER your_ip_address

You may need to bounce the 11.5 instance before it picks up the interfaces change, I can’t remember on that old version.

Set up a backup server on your local box (it should be set up automagically via the install process).

Then simply run the above SQL but with a directive to make the dump on your local box:
DUMP DATABASE your_db TO "/what/ever/path/file" AT REMOTE_BACKUP_SERVER

Remember that in this case it is backing up to your local box so make sure the path is updated accordingly.

[MattR]

Sybase provides a utility called 'sybmigrate' to migrate databases automatically. I've never used it, but it may work well here.

If none of those work, you may look and see if Sybase Central can provide a table dump...

If you have PHP (or another host language) set up on either the client or the server you can run something like this (if you do not have too many tables and/or rows):

<?php

$local  = sybase_connect( "localhost", "sa", "not4u"     );
$remote = sybase_connect( "remotebox", "sa", "alsoNot4u" );

$res = sybase_query( "SELECT col1
                             col2
                        FROM myRemoteTable" 
                     $remote );
                     
while( $row = sybase_fetch_array( $res ) )
{
    
    sybase_query( "INSERT INTO myLocalTable VALUES ( " . $row[ 'col1' ] . ",
                                                     " . $row[ 'col2' ] . ")" );
    
} // end while

Of course, if you have many rows you can run into a PHP timeout. So you may wish to ORDER BY a unique column so that you can get a restart point.

[freddydoesphp]

Yeah, thats the crappy thing about. I was going to have to wait for the DBA to get back from vacation, as I am trying to install a standalone version of a PowerBuilder App on a laptop. Its a government contract, so as you may or may not know getting things like access to a server become real challenges. So in short I am fairly new to the job and not on a payroll with the county, so I have to somehow resort to only database access over a VPN connection through Sybase Central or Infomaker. But no server access.

In fact I tried that with backing up the database but it puts the copy on the server, which I have no way to access at this time. Ah well! Whatcha gonna do.

[MattR]

Hmm, yes I know all about the Gov't. It seems like you are going to have to wait for the DBA, unless you can get something like PowerBuilder or PowerDesigner to talk to ASE -- then you can dump out the data via the utility.

As I said with DUMP DATABASE yes it will place them on the local box UNLESS you use the AT syntax. That way you can dump to a different backup server living on a different machine -- namely yours or one you have access to.

I'm not familiar with InfoMaker but can it extract data from the tables?

[freddydoesphp]

MattR, Thanks again for helping me out here. I do have PowerDesigner running on the machine I would like to rebuild the database on. Not sure if you agree but the sybase docs, seem to be one big jumbled mess. Could you refer me to the tool name in POwerDesigner wher eI should start. You are DA MAN! Thanks

[freddydoesphp]

More fun...

Well the only way I connect to the remote database is over a VPN connection, so when connected to the remote database I cannot access my local database. So this leaves me in an interesting predicament. I have gotten the tables built on the local side to mirror the remote side. But now I need to just move the data over. I now have PowerBuidler 8 PowerDesigner 9 and Infomaker 8. Do you know of any way to accomplish this with any of those tools. Thanks again Matt