Click here for Minisoft sponsor message

Adager release examines and repairs signed/unsigned
decimal data for client-server use

IMAGE/SQL uses signed and unsigned fields in its current version, and the database has never been designed to do any type checking. That means that when information gets passed to some client server programs, it can arrive in a format that the SQL standard doesn’t recognize: unsigned, a format which packed decimal and zoned decimal fields in IMAGE (P and Z types) use regularly.

Database administrators (DBAs) who need full SQL access to IMAGE/SQL must ensure all P and Z data in those databases is signed. If some of the decimal data is signed and some unsigned, the database is corrupt.

Joe Geiser, founder of HP 3000 client-server firm CSI Business Solutions and a member of the SIGIMAGE Executive Committee, said the unsigned problem can cause an IMAGE/SQL database to appear corrupted to some SQL tools. The situation can keep HP 3000 programs which communicate with PC clients from working.

“How many datasets are there out there that have unsigned numerics in them? I’ll bet there’s quite a few,” Geiser said. “Any kind of counter field, or something along those lines.

“This is one of a number of things out there that can cause data corruption,” he explained. “It sounds like a stupid little issue, but if you write data to the dataset and then you have PC programs that expect something else, then you’ve got corruption. If you’ve got a program that blows up, you’ve got data corruption.

“The ANSI SQL standard is that all numerics are signed. Even zero is a positive or negative. IMAGE can support unsigned numerics, just like an MPE file can support unsigned. In doing access using ANSI SQL, reading unsigned data will come in just fine – it’s writing and updating it that will cause you problems, unless you do some type casting. This is a bug with IMAGE/SQL, because it introduces data corruption, and it needs to be fixed.

“People say, ‘So what, just make it signed.’ Are you going to go back to all your programs and make them signed, and convert all your data? That’s crazy.”

A new version from Adager (208.726.9100) of its IMAGE/SQL transformation utility proposes to eliminate the craze on the database end, giving administrators a tool that changes selected unsigned fields to signed ones. Adager lab manager Fred White proposed, designed and developed the new library procedures, which support Examine Sign and Change Sign.

If a DBA chooses this route, all existing applications must be verified or modified to deal with the issue and always write signed decimals. The alternative is limiting SQL access to read-only access and having all decimal data unsigned.

The new feature leverages work delivered in earlier releases of the Adager transformation utility. The latest Adager makes it possible to change the signs of fields that are embedded, with bytes appearing before and after the target data.

“We allow people with these zoned and packed decimal fields to cruise through a database and impose a standard, either signed or unsigned,” said Adager’s R&D chief Alfredo Rego. “People may have mixed data. Some positives may be signed, some may be unsigned. For most COBOL applications it’s okay, because COBOL is really smart. But for the new breed of client-server applications that use Microsoft software, this really confuses them. They expect signed.”

Administrators can use the Examine Sign feature to scan databases in question, producing a log file that identifies entries which don’t match chosen criteria (either signed or unsigned). Change Sign then makes modifications to selected fields based on the Examine Sign results.