on 2011 Aug 09 12:35 PM
I would check out the dbvalid utility. You can call that externally or you can validate the database from Sybase Central.
From the SQL Anywhere 12 Help file...
With the Validation utility, you can validate the indexes and keys on some, or all, of the tables and materialized views in a database. You can also use the Validation utility to verify the database file structure to ensure that all pages in the database belong to the correct object, and that page checksums are correct. By default, dbvalid validates all the tables, materialized views, and indexes in the database and validates the database file structure.
When validating a table, dbvalid also validates all of the table's indexes to verify that the set of rows and values in the table matches the set of rows and values contained in each index. All BLOBs in the table are also traversed, BLOB allocation maps are verified and orphaned BLOBs are detected. The Validation utility also checks the physical structure of all index pages, the ordering of the index hash values, and the index's uniqueness requirements (if any are specified). Unless the -fx option is specified, each foreign key value is looked up in the corresponding primary key table to check that referential integrity constraints are intact.
When the -i option is specified, dbvalid validates each index in the object-list. Validating an index works exactly the same as validating a table, except that only the specified index and its underlying table are validated. If the index is a foreign key, each value is looked up in the primary key table unless the -fx option is specified.
If you start database validation while the database cleaner is running, the validation does not run until the cleaner is finished running. See sa_clean_database system procedure.
To run the Validation utility, you must have either DBA or VALIDATE authority.
You can also access the Validation utility in the following ways:
From Sybase Central, using the Validate Database Wizard. See Validate a database.
From Interactive SQL, using the VALIDATE statement. See VALIDATE statement.
The Validation utility can be used in combination with regular backups to give you confidence in the integrity of the data in your database. If you want to validate a backup copy of your database, it is recommended that you make a copy of the backup and validate the copy. Doing this ensures that you do not make changes to the file that is used in recovery. See Backup and data recovery.
Hope this helps.
Jeff Gibson
Intercept Solutions - Sybase SQL Anywhere OEM Partner
Nashville, TN
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
thanks Jeff I am coming from MSSQL so it is just enough different to seem like i don't know what the heck i am doing. hahaha Is their any other details on using this command in a script or a batch file? we are using dbbackup.exe to back up our database but their are no ways to invoke a validate. How do you schedule a check by the way? i am still trying to figure that out.
Have a look at dbvalid.exe, you could use this in a batch script.
so in conclusion to this thread you can use a ODBC with DBValid the final command i used was
dbvalid.exe -f -c -o E:IDMDBBACKUPDBValid.txt "dsn=OBDC_name"
this validates in the same script that does our backup. Sorry for all the Sybase newbness i guess on to the next problem hahaha. i get to beat up our vendor for their security practices with DB user rights.
Thanks guys for the help
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
For version 8, there is no support for maintenance plans (which are simply a GUI-based facility to create/maintain backup/validation events). However, the underlying gear does exist in v8, too (with some differences to newer versions, as usual...).
So you can do validation in several ways:
The v8 help does contain samples for all of these tasks.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
There is an internal mechanism which can detect bit changes on the underlying storage system. You can switch on database page checksums which will be evaluated each time a page of the db file is read from disk. See the Checksum option of the Create Database statement. Refer to Using checksums to detect corruption
Remeber, that Checksums are turned off by default in 11.0.1, so you have to explicitly enable this feature. In 12.0.1 they are turned on by default.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
What version are you using?
If you are using v10 or above and prefer to use a GUI, you can use Sybase Central to setup a "maintenance plan". This is an automated task (with SQL Anywhere, called an "event") than can be setup to run validation and/or backup on a regular schedule.
Cf. the docs.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
I can't tell for others, but I am using v8 since 2002 and have never had the need to do index maintenance - it's done automatically by the server. Note that there are situations (for example after lots of DELETEs) where indexes can become supotimal - cf. this description for v12.0.1...
We do try to answer questions on all SQL Anywhere versions so questions on v8 are absolutely welcome! (They are often tagged with "asa-8" as the product was named "Adaptive Server Anywhere" - in short ASA - for versions 6-9.) - So feel free to ask:)
I have another question about dbvalid, can dbvalid be used on a running database? searching around the big I i found something saying to make a copy of the DB file and then run dbvalid against the copy. I am trying to figure out why it is saying my database is invalid and was wondering if it was because it is online.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
well it looks like database is invalid might be a general error or some sort of permission error. i changed the user and then got the error DB is in use. so i made a test DB took it offline and then it worked. so is there an online option or parm? or is this a offline validate only?
thanks guys for the help on this. Chris
Validating a database requires that the database is running so there's no offline validation.
However, it is recommended to validate a "quiet" database - i.e. one not currently used by connections that do data manipulation. Otherwise the validation might notify false positives.
Therefore it is recommended to
Some possibilities include:
BTW: I would recommend to ask such "follow-up questions" as separate ones - IMHO these are good questions on their own:)
also when running dbvalid from the command line how does the server name need to be formatted? i keep getting server not found. also where do i find the name i need to put in there? the name i have tried do not seem to be working.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
ok so i am using C:Userscizatt>"C:Program FilesSybaseSQL Anywhere 8win32dbvalid.exe" -c "uid=user;pwd=pass;dbf-E:IDMDBBACKUPidmconsumer.db"
but i am getting an error Parse error: Missing '=' near 'dbf-E:IDMDBBACKUPidmconsumer.db"' not for sure what is wrong i used it right from sybase books any help would be awesome.
IMHO ist should be "dbf=E:IDMDBBACKUPidmconsumer.db" instead of "dbf-E:IDMDBBACKUPidmconsumer.db" (replace the dash with the equality sign). Maybe some path delimiters are missing too (e.g. E:\\IDM\\DBBACKUP\\idmconsumer.db)!?
looks like some of them didn't copy, this is the error with the = not for sure what it means by invalid
C:Program FilesSybaseSQL Anywhere 8win32dbvalid.exe" -c " uid=user;pwd=pass;dbf = E:IDMDBBACKUPidmconsumer.db" Adaptive Server Anywhere Validation Utility Version 8.0.1.3156 Specified database is invalid
User | Count |
---|---|
75 | |
10 | |
10 | |
10 | |
10 | |
9 | |
8 | |
7 | |
5 | |
5 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.