I'm trying to create a temp table in a SQL stored procedure on iSeries V5R1. I'm doing something like this:
DECLARE C1 CURSOR WITH RETURN FOR SELECT * FROM QTEMP.TEMP;
Create table QTEMP.TEMP LIKE LIBRARY.TABLE;
Insert into QTEMP.TEMP Select * from LIBRARY.TABLE where COLUMN = VENDORIDIN;
SET RESULT SETS CURSOR C1;
This works but has a couple of issues:
1) After the stored procedure is finished the temp table is still out there for 30 or seconds.
2) If I call a different stored procedure that tries to access the same temp table during that 30 seconds, it will find it.
What I want to do is like in SQL Server where I can create a work table like using #tmpTable that only exists for that stored procedure call and is not available to other stored procedures.
We are using VB.NET and HitSoftware OLEDB/AS400 as the data driver. Also, we are using the same UserID and Password to connect to the AS400 but are closing all connections after each call.
Does the AS400 not provide the same temp table functionality like we can do on SQL Server or I'm I doing something wrong?