dbunload help

[Former Member]

I am new to the Sybase world. I have a client that is running an application titled GeoGraphix which is a GIS based application of sorts. The GeoGraphix "projects" themselves point at sybase .db databases on the backend. Within the application, there is an option to rebuild the project databases. That option calls the dbunload utility. What I'm attempting to do is to automate that process but am having issues getting dbunload to run within iSql.

Just fyi - when the process does run, it spits out the following to a log of sorts:

SQL Anywhere Unload Utility Version 10.0.1.4051
Connecting and initializing
Unloading user and group definitions
Unloading table definitions
Unloading materialized view definitions
Unloading index definitions
Unloading functions
Unloading view definitions
Unloading procedures
Unloading triggers
Unloading SQL Remote definitions
Unloading MobiLink definitions
Creating new database
Unloading "DBA"."GX_SCHEMA_VERSION" (698 rows)
..... more Unloading of tables .....
and finally at the end:
Creating indexes

So the process seems relatively straightforward. Unload the database, reload it, re-create the indexes. Would this be something someone could guide me in automating? Again - I am new to the Sybase world so just looking for help/advice. Not even sure this is the correct forum, if not, apologies - please suggest a more appropriate location.

Thank you very much.

[justin_willey · Accepted solution]

Welcome to SQLAnywhere! There are two approaches to this either go with batch files and the command line utilities, or an isql script using SQL Statements - its really a matter of preference. My preference would be to go for the batch file approach if this is going to be something you are going to do a lot of - it's much easier to parametrise it! It would go something like this (untested):

REM unload the existing database structure and data
dbunload -v -c "UID=dba;PWD=*****;DBF=e:\\databases\\MyOldDatabase.db" -r "C:\\Unload\\reload.sql" -ii "C:\\Unload\\unload"

REM create a new database with default settings
dbinit  c:\\databases\\MyNewDatabase.db

REM reload the structure and data from the unload file
dbisql -c "UID=dba;PWD=***;DBF=e:\\databases\\MyNewDatabase.db" READ C:\\Unload\\reload.sql

Each of these command line utilities has a lot of optional parameters - you need to look at the help for details, so for example you can provide a non-standard dba password for the new database, pre-allocate disk space, use the correct language collation etc etc. They also generate exit codes so you could introduce error checking etc.

If the database is of significant size, it is well worth unloading to / reloading from a different (local) disk drive from where the database files are - the speed difference can be huge.

If the database is involved in a replication scheme you need to look at this help topic.

If you want to build the rebuild option into an application, you could go down the road of using the dbtools library.

[Former Member · Accepted solution]

Thank you very much Justin. I definitely appreciate the quick turnaround.

A few questions if you don't mind...

I assume that the reason this GeoGraphix company is even bothering with this is for Database compaction/Index re-application for performance purposes (the last step in their process is Creating Indexes thus why I ask). Would that seem realistic?

Another question - can I unload into a different(local) drive, then reload over the existing database? No need to dig in unless you are bored, just wondering if conceptually it is possible and I'll go work through the specifics.

I'm wondering, using a batch approach, how are the functions referenced i.e. how does dbunload/dbinit/dbisql get referenced? Are those just exes/utilities within a SQLAnywhere install directory?

Thanks.

[Former Member · Accepted solution]

Nevermind on that last part - I see the dbunload, dbinit, dbisql exes in the SQLAnywhere install folder (-:

[Former Member · Accepted solution]

I'm thinking this might meet my needs:

dbunload -v -c "uid=DBA;pwd=*;dbf=C:ProgramDataGeoGraphixProjectstestGXDB.db" -ar

"This option creates a new database with the same settings as the old database, reloads it, and replaces the old database."

Sounds like I might want to kill active sessions prior to running that particular step?

I'm confused about this though - "When the new database is created, the dbspace file names have an R appended to the file name to prevent file name conflicts if the dbspace file for the new database is created in the same directory as the dbspace for the original database. For example, if an unloaded database has a dbspace called library in the file library.db, then the library dbspace for the new database is library.dbR."

The first sentence in the description says it creates a new database with the same settings as the old database, reloads it and replaces the old database. Is that R file just a temp holding before replacement occurs?

Thank you very much!!

[Breck_Carter · Accepted solution]

In SQL Anywhere you can have up to 12 additional files containing tables and other data in addition to the main *.db file... that's what the Help is talking about when it gives the example of the library dbspace.

When the Help talks about "the dbspace file names have an R appended" it is referring to these secondary dbspaces, not the main *.db file.

For most people it doesn't matter... most folks don't bother with the secondary dbspaces, and there's usually no need for them.

[Former Member · Accepted solution]

Gotcha - thank you Breck. Another consideration I imagine I'm going to run into is to kill all active connections. Do you think I will have to take that into account?

[VolkerBarth · Accepted solution]

To cite from the dbunload doc page:

To unload a database, first ensure that the database is not already running. Then, run dbunload, specifying a DBA user and password, and referencing the database with the DBF= connection parameter.

So, yes, you should not only assure that there are no active connections but should generally stop the database altogether. That can be done from a batch file with the help of the DBSTOP utility - the exact usage will depend upon the question whether there are other databases running on the same server (so you would only stop the database in question) or not (than you would usually stop the database and the database server, as well).

If there are questions on that - I'd recommend to start a new question:)

[justin_willey · Accepted solution]

Unloading / reloading will help with performance if the database is having a lot of data added and removed. In such a case the internal structure of the database is likely to be come heavily fragmented and this is the easiest way of sorting that out. The actual database file itself is likely to get fragmented and while that can be fixed with a normal de-fragmenter, you can take the opportunity to pre-allocate space to a new un-fragmented db file.

In term of using different drives, yes you can - but do ensure you have a proper validated backup of the original database first!

[Former Member · Accepted solution]

Thank you all for your help getting started. I appreciate the fact that you all are not condescending like some other DBMS system's user bases can be (-: for the newbies.