Exception with parallel SQLJobs

[Chris Eibl]

Hi,
i'm running sheduled parallel sql-queries with this config:

    <scheduling:timer id="sql-timer" name="DATENLAGE Timer" xmlns:scheduling="http://rgordon.co.uk/oddjob/scheduling">
        <schedule>
            <schedules:daily xmlns:schedules="http://rgordon.co.uk/oddjob/schedules">
                <refinement>
                    <schedules:interval interval="00:10"/>
                </refinement>
            </schedules:daily>
        </schedule>
        <job>
            <parallel>
                <jobs>
                    <variables id="vars">
                        <connection>
                            <connection driver="com.ibm.db2.jcc.DB2Driver" password="password" url="jdbc:db2://host:port/db" username="user"/>
                        </connection>
                    </variables>
                    <sql id="WLSG18-PA-STATUS" name="WLSG18_PA_STATUS">
                        <connection>
                            <value value="${vars.connection}"/>
                        </connection>
                        <input>
                            <buffer><![CDATA[select 'WLSG18-PA-STATUS' as ITEMTYPE, YEAR(A.CREATETS_SRC) as TRANCHE, I.MIG_STATUS as STATUS, count(*) as ANZAHL 
            from WLSG18.POSTAUSGANG_ITEMS as I join WLSG18.POSTAUSGANG_ATTRIBUTES as A
            on I.ITEMID_ALT=A.ITEMID_ALT 
            group by YEAR(A.CREATETS_SRC),MIG_STATUS
            order by TRANCHE desc, MIG_STATUS asc]]></buffer>
                        </input>
                        <results>
                            <sql-results-sheet/>
                        </results>
                    </sql>
                    <sql id="WLSG18-PE-STATUS" name="WLSG18_PE_STATUS">
                        <connection>
                            <value value="${vars.connection}"/>
                        </connection>
                        <input>
                            <buffer><![CDATA[select 'WLSG18-PE-STATUS' as ITEMTYPE, YEAR(A.CREATETS_SRC) as TRANCHE, I.MIG_STATUS as STATUS, count(*) as ANZAHL 
            from WLSG18.POSTEINGANG_ITEMS as I join WLSG18.POSTEINGANG_ATTRIBUTES as A
            on I.ITEMID_ALT=A.ITEMID_ALT 
            group by YEAR(A.CREATETS_SRC),MIG_STATUS
            order by TRANCHE desc, MIG_STATUS asc]]></buffer>
                        </input>
                        <results>
                            <sql-results-sheet/>
                        </results>
                    </sql>
                </jobs>
            </parallel>
        </job>
    </scheduling:timer>

Now every so often this Exception pop's up in the Logs:

ERROR [pool-3-thread-390] (RunnableWrapper.java:178) [WLSG18_PA_STATUS] - Exception:
java.lang.RuntimeException: org.oddjob.beanbus.BusCrashException: No Connection.
        at org.oddjob.sql.ScriptParser.run(ScriptParser.java:178)
        at org.oddjob.sql.SQLJob.run(SQLJob.java:268)
        at org.oddjob.framework.RunnableWrapper.run(RunnableWrapper.java:172)
        at sun.reflect.GeneratedMethodAccessor38.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
        at java.lang.reflect.Method.invoke(Unknown Source)
        at org.oddjob.framework.DefaultInvocationHandler.invoke(DefaultInvocationHandler.java:103)
        at com.sun.proxy.$Proxy9.run(Unknown Source)
        at org.oddjob.framework.ExecutionWatcher$1.run(ExecutionWatcher.java:60)
        at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
        at java.util.concurrent.FutureTask.run(Unknown Source)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
        at java.lang.Thread.run(Unknown Source)
Caused by: org.oddjob.beanbus.BusCrashException: No Connection.
        at org.oddjob.sql.ParameterisedExecutor$1.busStarting(ParameterisedExecutor.java:85)
        at org.oddjob.beanbus.TrackingBusListener$1.busStarting(TrackingBusListener.java:21)
        at org.oddjob.beanbus.AbstractBusConductor.fireBusStarting(AbstractBusConductor.java:38)
        at org.oddjob.beanbus.BasicBeanBus.startBus(BasicBeanBus.java:72)
        at org.oddjob.beanbus.AbstractBusComponent.startBus(AbstractBusComponent.java:38)
        at org.oddjob.sql.ScriptParser.run(ScriptParser.java:171)
        ... 13 more

After the Exception it continues working ok again...
Do you have an idea whats causing it? Timeout on the Connection?

Regards,
Chris

[Rob Gordon]

Hi Chris, you need to ensure the variables job always completes before the sql jobs run. This can be achieved by using a sequential job as in:

timer
 sequential
  variables
  parallel
   sql
   sql

or even better:

sequential
 variables
 timer
  parallel
   sql
   sql

Regards,
Rob.

[Chris Eibl]

Hi Rob,
thanks for pointing out! I should've looked at the implementation first :-(

Regards, Chris