Skip to content

Instantly share code, notes, and snippets.

@BirgittaHauser
Created June 22, 2021 06:47
Show Gist options
  • Save BirgittaHauser/c73408ebe3351c68f8ffc0a1fb2c238e to your computer and use it in GitHub Desktop.
Save BirgittaHauser/c73408ebe3351c68f8ffc0a1fb2c238e to your computer and use it in GitHub Desktop.
Get the qualified object name for a unqualified specified object in the library list
-- I was recently asked how to get the qualified SQL/System Name for an unqualified specified object from the library list.
-- To make it easy I created a global variable to hold the Object Name
-- (a global variable can be populated with the SQL SET statement)
Create Or Replace Variable YourSchema.GblObjName VarChar(128) Default '';
-- In the following SELECT statement all Product and User Libraries in the library list are searched
-- for Programs, Service Programs and Files
-- If the content of the global variable is up to 10 characters the system name and SQL names are searched
-- otherwise only the SQL Names
-- The sequence of the libraries is considered
-- The first object that matches the search criteria is returned.
Select Ordinal_Position, System_Schema_Name, ObjName, ObjLongSchema, ObjLongName,
ObjType, SQL_Object_Type, ObjText, ObjAttribute
From Library_List_Info A Cross Join
Lateral (Select *
From Table (Qsys2.Object_Statistics(A.Schema_Name,
'*FILE, *PGM, *SRVPGM')))
Where Type In ('PRODUCT', 'USER') -- Product and User Libraries
And ( Objname = Case When Length(Trim(GblObjName)) <= 10 Then GblObjName End
or ObjLongName = GblObjName)
Order By Ordinal_Position
Limit 1;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment