SQL Alias visibility
We have found that creating an SQL alias works for accessing file members in embedded and interactive SQL. But if we create an ALIAS in our live libraries with the intention of keeping it for frequent use, how do we know it exists other than WRKOBJ? It doesn't show up in DSPDBR.
And is there really a benefit to not creating it in QTEMP everytime we want to use it?
And finally, is there a different way to access multiple data members in SQL statements other than ALIAS?
Software/Hardware used:
Software/Hardware used:
ASKED:
June 22, 2006 4:17 PM
UPDATED:
June 23, 2006 12:38 PM
Answer Wiki:
You can use the Over ride Data Base command to access a specific member in a multiple member file.
OVRDBF FILE(AGTDEC) TOFILE(QTEMP/AGTDEC) MBR(MEMBER0002)
STRSQL
SELECT * FROM AGTDEC
To see all answers submitted to the Answer Wiki: View Answer History.
Yes, OVRDBF is what we’ve used in the past but we want something easier to implement and manage. We found the ALIAS object is nice from within an interactive SQL session since you are referencing the member right there in your statement rather than managing the override state of your file. It’s more visible. It’s also easier than having to QCMDEXC the overrides in embedded SQL.
As far as doing a DSPDBR to find these, this won’t work as a Create Alias creates a local DDMF over the file and member. This does not show up in a dependent file listing.
However, there is a system file on QSYS library named QADBXREF which contains general information regarding files created on the system. The operating system maintains this file as you create, modify or remove files. In this file there are alias information fields that show the file, library and member that the alias name was created over. You should be able to query this by selecting any record where the alias fields are not blank and you should get all aliases on the system.
If you are going to access the same member off a file each time then you could leave the alias in a library other than QTEMP. However, if you use this to access random members and especially if you have this in procedures called my more than one user, you might want to go the QTEMP route. This way you can Create or Drop aliases on any member without affecting another user. The drawback is the QADBXREF file does not log files created in QTEMP libraries.