Use `create or replace alias` instead of just creating when opening members

[m-tyler]

Over one day of just working through how to use this extension, I noticed I had over 1000 QPJOBLOG SPLFs with USRDTA =QSQSRVR value. All or most from the same server job. Many of the job logs have this error message listed. "CPF327E Diagnostic 40 " "Alternative name for file PGMT_00038 not allowed." Then later on I see these "SQL0601 Diagnostic 30" "PGMT_SRCMAT_TEMP in ILEDITOR type *FILE already exists."

Somehow either the job logs need to be discarded, they need to not be created or better pre-checking is needed to prevent these errors. It takes more time than it should to remove these job logs, luckily we have an in house SPLF listing program where I can filter down to these SPLFs and mass delete the SPLFs.

In the last four entries, I only opened two source members.

[worksofliam]

@m-tyler

From the connections settings:

• What is your SQL executor set to?
• Do you have db2util installed?
• Do you have source dates enabled?

[worksofliam]

This could potentially be happening when creating aliases for members that already exist.

[m-tyler]

QPJOBLOG_6315_177676_QSQSRVR.txt

[worksofliam]

                                     From user . . . . . . . . . :   PGMT
                                     From module . . . . . . . . :   QSQALIAS
                                     From procedure  . . . . . . :   CLEANUP
                                     Statement . . . . . . . . . :   10122
                                     To module . . . . . . . . . :   QSQALIAS
                                     To procedure  . . . . . . . :   CLEANUP
                                     Statement . . . . . . . . . :   10122
                                     Message . . . . :   PGMT_SRCMAT_SRC007RG in ILEDITOR type *FILE already
                                       exists.
                                     Cause . . . . . :   An attempt was made to create PGMT_SRCMAT_SRC007RG in
                                       ILEDITOR or to rename a table, view, alias, or index to
                                       PGMT_SRCMAT_SRC007RG, but PGMT_SRCMAT_SRC007RG already exists.  All tables,
                                       views, aliases, indexes, SQL packages, sequences, global variables, masks,
                                       permissions, constraints, triggers, user-defined types, and XSR objects in
                                       the same schema must have unique names. -- If PGMT_SRCMAT_SRC007RG is a
                                       temporary table, it cannot be replaced unless the WITH REPLACE clause is
                                       specified. -- If the schema name is *N, this is a CREATE SCHEMA statement.
                                       If this is a CREATE TABLE or ALTER TABLE statement and the type is *N,
                                       PGMT_SRCMAT_SRC007RG is a constraint. Recovery  . . . :   Change
                                       PGMT_SRCMAT_SRC007RG to a name that does not exist, or delete, move, or
                                       rename the existing object. If this is a temporary table, use the WITH
                                       REPLACE clause. If creating an SQL package, specify REPLACE(*YES) on
                                       CRTSQLPKG. Try the request again.

There it is!

[m-tyler]

SQL executor is set to db2util.

Yes, I have source dates enabled.

[worksofliam]

Simple work around is going to be to use CREATE OR REPLACE ALIAS instead of just CREATE ALIAS.

https://github.com/halcyon-tech/vscode-ibmi/blob/0287ebe3d29285e31eb768dcdf5e6df2eb6b8de2/src/filesystems/qsys/complex/content.js#L39

Will work on this later. Should have a fix out by tonight.

[m-tyler]

Nice! Thanks!

[m-tyler]

Thinking about your solution you would need to be concerned about the IBM i server you are connecting with. Running a DROP ALIAS first would be most compatible and still achieve the same thing.

[worksofliam]

@m-tyler Good idea, but the DROP will also write to the log if the alias doesn't exist - which likely won't be as often.. but still will happen.

[m-tyler]

I dont have an issue with the CREATE or REPLACE for the ALIAS', our systems are up to date. just pointing out if someone uses this with an older system. I don't know the release that the "or REPLACE" came to be.

[worksofliam]

After some research, it looks like 7.2 supports CREATE OR REPLACE ALIAS. 7.2 is also the lowest version we support.

https://www.ibm.com/docs/en/i/7.2?topic=statements-create-alias

[worksofliam]

Tested and working

/home/alan3/lytx_rpgle_sdk/: echo "CREATE OR REPLACE ALIAS ILEDITOR.CMPSYS_VSCODE_RPGLINT for CMPSYS.VSCODE(\"RPGLINT\")" | LC_ALL=EN_US.UTF-8 system "call QSYS/QZDFMDB2 PARM('-d' '-i')"
{
    "code": null,
    "signal": null,
    "stdout": "DB2>\n  ?>\nDB20000I  THE SQL COMMAND COMPLETED SUCCESSFULLY.",
    "stderr": ""
}

[worksofliam]

Out in 0.8.33 now.