Sybase and Nulls

If you have not read about how PowerBuilder treats nulls, you may want to read  nulls first.

Guaranteeing a Singleton

Because the datetime on the client machine may not be accurate, I wanted to write a function to get the current date and time from the server.  

For Sybase DBMS, this is simply achieved with the GetDate() function.  The problem is developing the SQL to return exactly one row everytime the function is called.  (It will still work if the SQL returns multiple rows, but you will have a lot of unecessary database traffic.)

Why Does the Show Plan Not Use an Index?

A table scan will be done in lieu of an index if:

• One or more of the columns composing the index is primarily populated with nulls
• The table has a small number of rows.
• A join is being done between different data types (int and numeric(10.0) for example)

Avoiding the Use of Cursors in a Stored Procedure

This example Stored Proc (Sybase DBMS) loops through a result set one row at a time without the use of a cursor. It also has a recursive call.

Sybase Error: 2403 “WARNING! Some character(s) could not be converted…

I was working on a application that I am not too familiar with. While running the app in the PowerBuilder developer environment, I was getting the following database error:

Database error code: 2403

Select error: WARNING! Some character(s) could not be converted into client’s character set. Unconverted bytes were changed to question marks (“?”).

Temp Tables

How do I destroy a temp table (Sybase)

Example:

Extended Attributes

What is the PowerSoft Repository?

The Powersoft repository (AKA Powersoft Catalog) is a series of 5 tables containing extended attributes pertaining to a data table and its columns. These attributes contain information such a s labels, headings, display formats, validation rules, and edit styles.

Do not confuse the database catalog with the repository. The database catalog contains column definitions, initial values, constraints, and referential integrity rules and has no relationship with the PowerSoft repository.

How can I modify a table with out dropping it and clobbering the extended attributes?

1. Use the “Object|Export syntax to log” menu selection to save the data table syntax to the painter log.

2. Select Design|Save Log As to save the log to a file. The log contains both the syntax and INSERT statements that populate the repository with the extended attributes for the table.

3. Make changes to the SQL statements in the saved file, import the file into the Database Administration painter notepad, and execute the statements to recreate the table and populate the repository.

Or you could use a database design tool

How do I synchronize the repository?

When data tables are deleted outside the Database painter, for example using DROP table from SQL Plus, extended attributes associated with the deleted table remain in the repository. This information is now orphaned and takes up space.

To remove orphaned attributes from the repository, select Design|Synchronize PB Attributes in the Database painter. This menu items compares all attribute information to the list of existing tables and purges attributes for tables that no longer exist.

Warning: make sure you have access to all the data tables, otherwise you risk deleting extended attributes for tables that you can’t see.

What is DWEAS Utility?

The DataWindow Extended Attribute Synchronizer (DWEAS) lets you update many of the attributes of an existing DataWindow object with the current extended attribute values from the Powersoft repository.

This is important because when a new DataWindow is built, its extended attributes are gathered from the current values in the repository. When the values change, the object attributes are not automatically updated.

Transaction Object

Properties for database communication

Property	Type	Description
DBMS	String	Name of the DBMS
Database	String	Name of the database
UserID	String	User ID for database logon
DBPass	String	Password
Lock	String	Isolation level
LogID	String	User ID for server logon
LogPass	String	Password
ServerName	String	Name of the server
AutoCommit	Boolean	Turns transaction processing on or off, where supported
DBParm	String	DBMS specific settings

“Live” Properties

When you assign a value to most properties, the value is stored and no immediate action is taken. However, AutoCommit and DBParm are “live” properties. If you are connected to the database, and you assign a value to either of these properties, a message is sent to the database.

Properties that return status information

Property	i) Type	Description
SQLCode	long	Status code for the last SQL operation 0 Ok 100, No result set -1 error
SQLNRows	long	Number of rows affected by most recent SQL operation.
SQLDBCode	long	DBMS specific error code
SQLErrText	string	DBMS specific message
SQLReturnData	string	DBMS specific

Database Glossary

Concurrency

Several clients sharing a resource

Contention

Multiple users try to access a resource at the same time resulting in waits or event deadlocks.

Deadlocks

A deadlock occurs when two or more transactions are both in a wait state, with each one waiting for the other to release a lock before it proceeds.

Deadlocks are automatically detected and resolved by the DBMS. The DBMS rolls back one of the transaction usually the one with the least amount of CPU time.

Exclusive lock

A DBMS always places exclusive locks whenever a transaction performs an update operation (insert, update, delete)

A transaction cannot obtain an exclusive lock on a page or row while other transaction has any lock (shared or exclusive)

A DBMS holds an exclusive lock for the duration of a transition (until a COMMIT or ROLLBACK)

Lock Granularity

Identifies the lowest level at which a DBMS provides locking. Some DBMS offer locking at the row level while others do so at the physical database page level

Optimistic concurrency

The updated rows are checked to determine if another user has changed them since they were originally read. If the data has been updated by someone else, the transaction is rolled back and a message informs the user accordingly.

This technique assumes that the likelihood that two users will attempt to select and update the same information at the same time is low. In such cases, it also accepts that one user may lose his or her updates.

Pessimistic concurrency

Is implemented using a database management system’s locking facility to acquire and leave locks on the rows being read. A user is guaranteed data consistency because no other user can update the data while it is locked.

Pessimistic concurrency is used when the likelihood that other users will update the same data is high and absolute isolation between users is required. This type of control comes at the expense of concurrency. The can be significant with DBMS using page locking.

Shared Lock

Some DBMS place shared locks on pages while they are being read after a transaction issues a SELECT statement, and then releases them after reading each page or row.

A transaction cannot obtain a shared lock on a page while another transaction has an exclusive lock on it. However, multiple transactions can obtain shared locks on the same page or row.