I have a Stored procedure with 2 IN parameters (dbname,schemaname) and 3 OUT parameters one for the count of calls to the 2nd Stored procedure and the other 2 to capture the error messages upon error handled. The procedure gets all the tables in the schema through a cursor and calls another Stored procedure by passing in the dbname,schemaname and table name as 3 parameters, which will insert one row per table into a metadata table.
I can see 10 rows getting inserted into the table and nothing happens after that. It is always 10 rows but the process should process around 150 rows.
I am either getting an error - SQL0954C Not enough storage is available in the application heap to process the statement.
Or at times it is just hanging up and no response. I had to ask the DBA to unlock the tables when ever it is getting struck.