SQL Anywhere for Linux - How to Automate remote ASA 7/8/9 updates/upgrades?

Author

How to Automate remote ASA 7/8/9 updates/upgrades?

Susanb @MedVantx

2005-04-05, 8:03 pm

We have been tasked with created an automated remote
application and/or database update (ddl, triggers, stored
procedures, functions, etc).
Can anyone point me in the right direction of documentation
on how to do this?
If dbisql within a shell script is the answer, how can we
access the database data dictionary to validate that the
"automated" dbisql changes have been successfully applied?
Would database upgrades be done the same way?
(has anyone already created opensource to do this?)
thanks.

Breck Carter [TeamSybase]

2005-04-05, 8:03 pm

You are asking about a large and open-ended topic where the effort
ranges from "merely difficult" to "virtually impossible" depending on
what kind of updates you want to automate and whether you want full
"lights out" automation or can tolerate manual intervention.

And whether or not you are talking about databases that are
participating as MobiLink remotes... THAT raises the bar a bit :)

MobiLink version 9 introduced the sp_hook_dbmlsync_sch
ema_upgrade
which lets you build a schema upgrade mechanism into the
synchronization process. You still have to write the upgrade commands,
and the code to apply them; the hook procedure merely ensures it gets
executed at the appropriate point in time (after synchronization is
complete, before the table locks are released).

Otherwise you are on your own. One possibility is to deploy an empty
upgraded database file and run some code to migrate the data across;
proxy tables are often used for migration purposes, as are UNLOAD and
LOAD commands. Another possibility uses a remote database connection
from head office together with FORWARD commands to push DDL commands
out to the remote database.

Are you talking about upgrading multiple V7 and V8 databases to V9?

Is MobiLink involved?

The Version 9 Help has some sections of interest:

=====
What's New in SQL Anywhere Studio
Upgrading Software and Databases
Upgrading Adaptive Server Anywhere
Upgrading a database
=====

=====
What's New in SQL Anywhere Studio
Upgrading Software and Databases
Upgrading MobiLink
Upgrading Adaptive Server Anywhere MobiLink clients
=====

=====
MobiLink Administration Guide
Synchronization Techniques
Schema changes in remote databases
Adaptive Server Anywhere remote databases
=====

You can query the system tables (SYSTABLE, SYSPROCEDURE, etc) to
determine if all the expected changes worked. FWIW proxy tables work
for system tables as well.

Breck

On 5 Apr 2005 10:48:03 -0700, Susanb @MedVantx wrote:

>We have been tasked with created an automated remote
>application and/or database update (ddl, triggers, stored
>procedures, functions, etc).
>Can anyone point me in the right direction of documentation
>on how to do this?
>If dbisql within a shell script is the answer, how can we
>access the database data dictionary to validate that the
>"automated" dbisql changes have been successfully applied?
>Would database upgrades be done the same way?
>(has anyone already created opensource to do this?)
>thanks.

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Mark Culp

2005-04-05, 8:03 pm

All of the information about the database schema is stored in
system tables which can be accessed via normal SQL statements.
Take a look in the docs under:
ASA SQL Reference
System Tables
for more information about the tables. Note that these tables
are read-only.
SYSPROCEDURES stores the procedure/function definitions.
SYSINDEXES stores the information about indexes (but not PK/FK)
SYSTRIGGER contains the information about table triggers.

To do your update, you can either build your own application
or just use dbisql scripts - your pick ! :-)
--
Mark Culp
ASA Research and Development
-------------------------------------------------------------------------
** Whitepapers, TechDocs, bug fixes are all available through the **
** iAnywhere Developer Community at http://www.ianywhere.com/developer **
-------------------------------------------------------------------------

Susanb, @MedVantx wrote:
>
> We have been tasked with created an automated remote
> application and/or database update (ddl, triggers, stored
> procedures, functions, etc).
> Can anyone point me in the right direction of documentation
> on how to do this?
> If dbisql within a shell script is the answer, how can we
> access the database data dictionary to validate that the
> "automated" dbisql changes have been successfully applied?
> Would database upgrades be done the same way?
> (has anyone already created opensource to do this?)
> thanks.

SusanB MedVantx

2005-04-05, 8:03 pm

Yes we are realizing that it is a virturally impossible
task.
At each "remote" site, we have custom java applications
running on one or more client PCs (currently windows) which
in turn connect (via jdbc) to a single ASA(7) db on a linux
box. This is done to allow the site to operate without
being connected to the "corporate" network.
All of these remote ASA dbs participate in some limited
Mobilink (some table are site specific only, we push some
data down and we get back transaction historical data).
We have tried using the dbisql command -but it is not pretty
even with the -q switch and redirecting the output.
Sometimes we'll only need to "update" the individual client
applications, at other times, db schema changes (new
columns, procedures, functions, etc) and then there are db
upgrades (like from v7 directly to v9).
I will make a point to look at the
sp_hook_dbmlsync_sch
ema_upgrade and the documentation that
you pointed out.

Thanks.
BTW I think you originially helped us get this all set-up
(when we were OnsiteRX). The original set-up has been
running very well for us with minimal "fixing".

Breck Carter [TeamSybase]

2005-04-05, 8:03 pm

Yes, I visited you folks back in late 2001, it was nicer weather than
here in Toronto :)

You could "grow your own" dbisql with EXECUTE IMMEDIATE statements
working on strings, for the purpose of applying DDL changes to a
database. These operations could be performed from within a stored
procedure or event that looked for changes stored in a table.

Let me know if I can be of any help.

Breck

On 5 Apr 2005 12:22:00 -0700, SusanB MedVantx wrote:

>Yes we are realizing that it is a virturally impossible
>task.
>At each "remote" site, we have custom java applications
>running on one or more client PCs (currently windows) which
>in turn connect (via jdbc) to a single ASA(7) db on a linux
>box. This is done to allow the site to operate without
>being connected to the "corporate" network.
>All of these remote ASA dbs participate in some limited
>Mobilink (some table are site specific only, we push some
>data down and we get back transaction historical data).
>We have tried using the dbisql command -but it is not pretty
>even with the -q switch and redirecting the output.
>Sometimes we'll only need to "update" the individual client
>applications, at other times, db schema changes (new
>columns, procedures, functions, etc) and then there are db
>upgrades (like from v7 directly to v9).
>I will make a point to look at the
> sp_hook_dbmlsync_sch
ema_upgrade and the documentation that
>you pointed out.
>
>Thanks.
>BTW I think you originially helped us get this all set-up
>(when we were OnsiteRX). The original set-up has been
>running very well for us with minimal "fixing".

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Sponsored Links