what is the equivalent to MSSQL DBCC checkdb in sybase?

asked 09 Aug '11, 11:35

izatt82's gravatar image

izatt82
1163410
accept rate: 0%

edited 09 Aug '11, 17:10

Volker%20Barth's gravatar image

Volker Barth
30.3k300452659

FYI: I guess it's quite familiar (and recommended) to use DBCC DBREINDEX with MS SQL databases on a regular schedule in order to (re-)optimize indizes. - AFAIK that's less necessary with SQL Anywhere - the latter has much more focus on self-administration and self-optimization...

(10 Aug '11, 16:45) Volker Barth

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

permanent link

answered 09 Aug '11, 12:36

Jeff%20Gibson's gravatar image

Jeff Gibson
1.4k284454
accept rate: 21%

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.

(09 Aug '11, 14:22) izatt82
Replies hidden
1

Have a look at dbvalid.exe, you could use this in a batch script.

(10 Aug '11, 03:40) Reimer Pods

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.

permanent link

answered 09 Aug '11, 17:08

Volker%20Barth's gravatar image

Volker Barth
30.3k300452659
accept rate: 32%

edited 09 Aug '11, 17:11

It's adaptive server anywhere 8

so you guys don't do index maintenance in sybase? I am not for sure if i am in the right spot for ASA 8. sorry for being such a sybase newb.

(11 Aug '11, 10:02) izatt82
Replies hidden
1

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:)

(11 Aug '11, 16:39) Volker Barth

thanks, this is a new product they threw at me so i am trying to wade my way through it.

(15 Aug '11, 10:18) izatt82

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.

permanent link

answered 10 Aug '11, 03:49

Martin's gravatar image

Martin
8.6k116150237
accept rate: 14%

edited 26 Jan '15, 11:47

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:

  1. Use the DBVALID utility (as others have suggested) - it runs as a separate executable and can be embedded in batch files.
  2. Use the VALIDATE TABLE and VALIDATE INDEX statements (or the sa_validate() system procedure which validates all tables) - these are SQL statements and can be run in a SQL batch or stored procedure.
  3. Incorporate the SQL statements from 2. in an event to make them run automatically on a regular schedule (so that no explicit connection is needed).

The v8 help does contain samples for all of these tasks.

permanent link

answered 11 Aug '11, 16:55

Volker%20Barth's gravatar image

Volker Barth
30.3k300452659
accept rate: 32%

Thanks Volker it seems pretty bare bones. i will most likely use it in my backup script that is already running.

(12 Aug '11, 15:07) izatt82

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

permanent link

answered 17 Aug '11, 13:39

izatt82's gravatar image

izatt82
1163410
accept rate: 0%

edited 17 Aug '11, 13:42

I assume your command does work, however, DBVALID option -f was deprecated in v10, and the "DSN=..." part would belong directly after the -c..., so the following would be more common:

dbvalid.exe -c  "DSN=OBDC_name" -o E:\IDMDBBACKUP\DBValid.txt
(17 Aug '11, 17:04) Volker Barth

yeah that works too. i am V8 so -f is still ther, but i also found an even older sybase DB in another system the gui look like it is from win 3.1 not joking. this is the path to the config if that tells you anything

(17 Aug '11, 17:33) izatt82
Replies hidden

Ah I see, I had mistakenly thought you were using v12.

FWIW, I still do use v5.5.05 in a legacy 3rd party app - still rock solid... Though I don't use the SQL Central GUI, just dbisqlc - which still does like similar in 12.0.1:)

(17 Aug '11, 17:42) Volker Barth

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.

permanent link

answered 15 Aug '11, 10:19

izatt82's gravatar image

izatt82
1163410
accept rate: 0%

i found this dbvalid -c “uid=DBA;pwd=sql;dbf-c:sybaseIQ-15_1demoiqdemo.db”

but i would prefer to not store the password in clear text and not type it in when the script runs. any ideas on that?

(15 Aug '11, 10:34) izatt82

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.

(15 Aug '11, 10:45) izatt82
1

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)!?

(15 Aug '11, 11:25) Reimer Pods

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

(15 Aug '11, 13:43) izatt82

oh and it is "e:idmbackupidmconsumer.db" well the comment box also parse out backslashes so i guess that is the problem.

(15 Aug '11, 13:45) izatt82

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.

permanent link

answered 17 Aug '11, 11:40

izatt82's gravatar image

izatt82
1163410
accept rate: 0%

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

(17 Aug '11, 12:01) izatt82
1

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

  1. either validate the production database when it is not actively used (say, nightly)
  2. or better validate the copy of a backup (not the backup itself!).
(17 Aug '11, 12:07) Volker Barth

thanks for the quick answer Volker, so the next issue is how can i not have the username and password in the command string? i would rather not have a DBA account in clear text. not for sure if there is a way to use an ODBC or something to that effect.

(17 Aug '11, 12:31) izatt82
Replies hidden
1

Some possibilities include:

  1. Let the validation run within an event (so no external connection/login is needed).
  2. Use a configuration file as input for DBVALID and use DBFHide to obfuscate the credentials.
  3. Create a particular database user who has only the VALIDATE priviledge (i.e. GRANT VALIDATE TO ...), and connect with that user when validating (possibly in conjunction with 2.). In case someone would get these credentials, they won't be able to do much harm...

BTW: I would recommend to ask such "follow-up questions" as separate ones - IMHO these are good questions on their own:)

(17 Aug '11, 12:59) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×28
×27

question asked: 09 Aug '11, 11:35

question was seen: 5,899 times

last updated: 26 Jan '15, 11:47