Re: SQL0805N Package "NULLID.SYSLN303 0X5359534C564C3031" was not found. SQLSTATE=51002 error

[rodeh65]

Certain packages like SYSLN require multiple copies due to the high usage of them. By default there are three copies made for these. What happens is an application is using a lot of cursors, sometimes because of application programming where cursors aren't being closed. When this
happens the application will exceed the number of cursors that the 3 copies of the SYSLN package can serve. And it tries to use SYSLN303 (or a 4 th copy). That wasn't bound though so it doesn't exist. In order to get it to exist you simply bind db2cli.lst with CLIPKG more than 3 with a range as high as 30.

Please you can bind with the following

"db2 bind db2cli.lst ... clipkg 5 ... "

[aero...@mexicali.gob.mx]

I did:
bind C:\SQLLIB\bnd\@db2cli.lst blocking all clipkg 10

Now i'm getting:
IBM.Data.DB2.DB2Exception was unhandled
Message="ERROR [57011] [IBM][DB2/NT] SQL0954C Not enough storage is available in the application heap to process the statement. SQLSTATE=57011\r\n\r\nERROR [57011] [IBM][DB2/NT] SQL0954C Not enough storage is available in the application heap to process the statement. SQLSTATE=57011\r\n"
Source="IBM.Data.DB2"
ErrorCode=-2147467259
StackTrace:
at IBM.Data.DB2.DB2Connection.a(IntPtr A_0, x A_1, t A_2)
at IBM.Data.DB2.DB2Command.b()
at IBM.Data.DB2.DB2Command.ExecuteNonQuery()
at LDAPlistWin.Form1.addContact(DirectoryEntry de) in D:\Aplicaciones\LDAPlist\LDAPlistWin\LDAPlistWin\Form1.cs:line 112
at LDAPlistWin.Form1.searchContactsLDAP(String ou) in D:\Aplicaciones\LDAPlist\LDAPlistWin\LDAPlistWin\Form1.cs:line 47
at LDAPlistWin.Form1.searchContactsLDAP(String ou) in D:\Aplicaciones\LDAPlist\LDAPlistWin\LDAPlistWin\Form1.cs:line 45
at LDAPlistWin.Form1.searchContactsLDAP(String ou) in D:\Aplicaciones\LDAPlist\LDAPlistWin\LDAPlistWin\Form1.cs:line 45
at LDAPlistWin.Form1.button1_Click(Object sender, EventArgs e) in D:\Aplicaciones\LDAPlist\LDAPlistWin\LDAPlistWin\Form1.cs:line 29
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(Form mainForm)
at LDAPlistWin.Program.Main() in D:\Aplicaciones\LDAPlist\LDAPlistWin\LDAPlistWin\Program.cs:line 17
at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()

[leoem...@ironridgetech.com]

After wrestling with this problem, I finally realized what is causing it. I have a java application that retrieves a ResultSet and then issues a prepare for an additional sql statement per row in the first resultset. Although I closed the ResultSet object when I was done with it,I did not explicitly close the Statement object. The result of this is that each prepare would take an additional section from the SYSLN, SYSLH, SYSSH plans in sequence until the number of plans and sections were exhausted. At that point, you get the above message. If you are running with the default size of 3 on the db2jdbcbind command, you can increase the number of copies of these plans. THIS IS ONLY A SOLUTION IF YOUR PROGRAM REALLY NEEDS MORE THAN THE DEFAULT NUMBER OF PLAN/SECTIONS. If your problem is like mine and you are not explicitly closing the Statement object, increasing the number of plans may get you by, but if the number of rows you need to process increases over time, you may eventually exhaust the pool of plans again. This is seen in some of the posts where increasing the pool size seemed to work, but the problem came back over time. It also shows up in cases where the problem occurs after a certain number of rows are processed. I was able to see the problem by turning on the db2cli trace and JDBCTrace. You can see the plan and section number used on each execution of the statement increase until the pool is exhausted. If you re-run with explicit Statement object close - the problem of increasing plan and section numbers disappears from the trace and so does the exception. As I mentioned earlier, you may have an application that requires due to nested processing(highly unlikely, but possible - more likely several application with high dynamic SQL usage), more open Statement objects at one time than is supported by the default size value of three. This actually gives you 18 plans with upwards of a couple of thousand sections available to your applications. You can see this by looking in the SYSPLAN table where you will see an entry for each of the different plans (SYSSN200, SYSLN201 SYSSH300 etc.). One of the columns indicates the sections available in each plan.I might also add that without issuing the Statement object close, you could be faced with a java memory leak as well depending on how high you set the size parm to, as the java object backing the Statement object will not get GCed until it is closed.
So, based on my experience - I would review the code first and make sure explicit Statement object close is issued - the sequnce being prepare, execute, possibly iterate a resultset, close the resultset, close the Statement object, before increasing the size on the db2jdbcbind command.

[abou...@ca.ibm.com]

Hi,
I have a very simple SQL script that simply does 50,000 inserts. with a "commit" after every 2000 inserts. My script failed with that error and I am trying to understand why. Shouldn't the "commit" immediately close the cursor? I rebound using your suggestion but I still got the same error.
The only solution was to break down that script into 5 scripts of 10,000 inserts each. Can you please explain why I hit the problem and why is it fixed when I broke it down. I am running all the inserts in sequence so everything is single threaded, no concurrencies. Thanks