On 21-Oct-2011 08:41 , Raul A. Jager W. wrote:
Actual version 7.1
I want to re-create a table using SQL, and I want to be able to use
the programs that where compiled before the change. The old table was
created in the 90's using dds.
To "want to" implies no valid requirement for change. Change for the
sake of change?
Mi problem is that a DATE field was defined in the *DMY format, and
SQL insist in creating *ISO. I tryed "set option datfmt=*dmy" I tryed
the parameter in the RUNSQLSTM, the SYSVAL is DMY, in an interactive
SQL I used f13 to set DMY, but "create table" ignores all. I even
tryed "create table like old..."
Numeric fields created by DDL have no numeric editing. This is
effectively the same issue. There is no requirement in SQL for any
particular date format; i.e. there is no meaning to SQL. If someone
wants to format a DATE data type value in a particular supported format,
the CHAR scalar function provides several representations for dates.
The reason why I want to re-create the table now is that when I
tried to set a constraint I got a message "The table is in use" even
when I had it allocated *EXCL. Digging into messages I found "5
-Object is damaged" and in the actions it recommends to delete and
restore or create.
A user can allocate only the individual physical data of any database
file member(s) with the *EXCL lock level. The user can allocate the
actual database *FILE object only with the *SHRRD, and IIRC the same
locking for the member. If a file is locked exclusive while not being
created or modified under isolation, then any exclusive lock is likely
to exist only as the side effect of a failure, possibly by origin of
defect or damage, in a job that is still active; then the database file
is likely tracked under "database recovery" which means the object is
logically damaged pending completion of the previously failed operation.
Very possibly the first attempt to add the constraint failed in a
manner which was not a standard failure, but a "function check" which
left the object logically damaged. The "damage" message recovery is
accurate, but logical damage may be able to be corrected if the origin
for the unexpected failure is resolved, and the pending recovery is
allowed to complete without further error.
What actually transpired is of course logged in joblog(s), history,
the dbxref, and reflected in the [pending] condition of the *FILE
objects of the database network\relations. For "words" being used to
describe what "damage" errors were received instead of actual details
from a joblog and history, any alluding to possible origin in my [above]
comments are purely speculation.
Is there a way to create a DMY format date using SQL?
The DATE data type will always be *ISO irrespective of default
presentation and\or date string literal preferences for non-standard
specifications of date value constants.
Should I make the change and re-compile everything?
The only recovery to physical damage and much of logical damage is to
first delete [and ensure completion; i.e. no "errors were ignored during
delete"; on occasion, this may not be obvious except by errors during
create which suggest prior DROP activity was incomplete] and then create
the file(s) anew. Note: although some "damage" may prevent some access
to the objects, often data is still available, so sometimes the order of
actions is reversed and a rename or move phase is added.
With an "old" DDS file for which some desire and reasonable
expectations of benefits plus understanding of caveats [e.g. no choice
in DATFMT] exist for performing that action, creating the file(s) anew
[with DDL] is a reasonable action anyhow, admittedly. Because over the
various releases for the conversions and change\restore activity against
the file with past defects involving those actions possibly concealing
future difficulties [e.g. causing ADDPFCST or ALTER ADD CONSTRAINT to
exhibit unexpected errors], creating the new version of the file gives a
"clean slate".
Regards, Chuck
midrange.com
