Re: SQLRPGLE create table

Realy, I can not add the constraint, and the system reported some damage in the object. So, I need to do something. Either re-create with DDS or do it with SQL (wich I think it is better)
Maibe a RCLSTG will fix the file, but I prefer to re-create and have it "clean".
I consult the list to know my options, and get some advise. Since the file had the 8 chars date, I expected to be able to create it new with the same format.
I feel that by doing nothing I have a "worst problem waiting to happen".

CRPence wrote:

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