AS400 SQL query is similar to CLRLIB (clear library) on native AS400
I am working on an AS400 database and I need to manipulate a library / collection using sql.
I need to recreate something similar to the CLRLIB command, but I haven't found a good way to do it.
Is there a way to delete an entire table from a library using a SQL query?
Perhaps I can delete the collection and create a new one with the same name. But I don't know if this is a good way to clean up the library.
POST: Thanks to Buck Calabro for solving it.
I am using the following query to call CLRLIB in SQL:
CALL QSYS.QCMDEXC('CLRLIB LIB_NAME ASPDEV(ASP_NAME)', 0000000032.00000)
Where LIB_NAME is the name of the library I want to clear, ASP_NAME is the ASP name where the library is located and 0000000032.00000 is the length of the command.
source to share
(note that the term COLLECTION is deprecated, SCHEMA is the current term)
Since the library can contain both SQL and non-SQL objects, there is no SQL way to remove all possible object types.
Deleting the schema and re-creating it might work. Note, however, that if a library is in the list of job libraries, it will be locked and you cannot delete it. Also, if the library was not originally built via CREATE SCHEMA (or CREATE COLLECTION), you will run into differences.
CRTLIB creates an empty library, CREATE SCHEMA creates a library plus the objects needed for automatic logging, and about a dozen SQL system views.
source to share
Read Charles's answer - there may be objects in your schema that you want to store (data areas, programs, display and printer files, etc.). If the problem is dropping all tables so you can rebuild all tables, then look at the various system catalog tables: SYSTABLES, SYSVIEWS, SYSINDEXES, etc. The system catalog "knows" about all tables, indexes, views, stored procedures, triggers, and so on. You can read the directory and issue the appropriate SQL DROP statements.
source to share