Coding a Wait loop

[arst06d]

Hi

I have a DTS package that runs every night. It connects to the AS400 via ODBC and sucks data into SQL Server. I need it to wait until the overnight processes on the 400 have finished before doing its work, and can determine this by querying a table on the 400 where a completion flag is set.

So - How do you code a wait loop in DTS (using SQL 2000):
- wait for 1am say
- Test for completion flag on 400
- If set, continue
- If not set, wait another 15 mins

Up til now I've been using an ExecuteSQL task which issues a WAITFOR command for 6am when I'm pretty sure the overnight processes have finished. But the execution time is starting to creep over 9am which is not acceptable.

Thanks in advance.

 

[leveyp]

Not sure about delays in DTS.
I normally get the AS400 to run the DTS job when it's finished doing whatever. Have a word with your nearest AS400 tech about RUNRMTCMD (Run Remote Command) All you need to do is create a .BAT file containing DTSRUN commands and then get the AS400 to run it.

 

[dbomrrsm]

you can create a SQL task using

net pause mssqlserver
WAITFOR TIME '00:10'
net continue mssqlserver

with the loop in the DTS going back to the job.



[blue]DBomrrsm[/blue]

 

[arst06d]

Hi again
Thanks to DMomrrsm and leveyp for their comments.
I'v tried to code a wait loop by dynamcally setting package tasks back to a wait state, but it all went badly wrong and I've given that up.
(I believe sql2005 DTS has a wait loop construct, but it doesn't help me at this point ...)

My choices now appear to be:

Code the wait in an activex script task (connect to 400, check the values in the tables, check again if not set)

OR

use leveyp's suggestion the RUNRMTCMD.

My questions now:

in an activex task, how do I do the equivilent of SLEEP? ie suspend processing for x seconds. I would usually use an API in VB proper.

and

Anything I should watch for using RUNRMTCMD? I presume I pass the IP address of the sql server and the command is "DTSRUN " with switches. Is that it?
Since the RUNRMTCMD will be issued at the end of the overnight process on the As400, will the sql server allow the 400 to access the DTS package? Does the AS400 have to be granted permissions to the sql server?

many thanks.

 

[leveyp]

Have a look at

http://www-912.ibm.com/s_dir/slkbase.NSF/0/26e8d8b78808bc46862566ba00595402?OpenDocument

The syntax in a CLP program is simply:

PGM
RUNRMTCMD CMD('c:\temp\run.bat') RMTLOCNAME('192.168.12.231' *IP) +
RMTUSER(TEST) RMTPWD(TEST)
ENDPGM

 

[dbomrrsm]

WAITFOR TIME '01:00'
WHILE (select completedflag from AS400) = NotCompleted
BEGIN
WAITFOR DELAY '00:01'
PRINT 'Waiting for a minute'
CONTINUE
END

This will check the AS400 (not sure how you code the getting of this flag - maybe need to update a table in SQL server with this flag when complete then use this as the check)

and if the flag is set to notcompleted will wait for 1 second then continue to check again. so as soon as the flag is not Notcompleted the batch completes.

you can then continue your DTS packeag on completion of the above SQL task.

hope this helps.

[blue]DBomrrsm[/blue]

 

[leveyp]

The suggestion from DBomrrsm will of course do the trick. You would need the CLP driver to wtite / uodate a record in a dummy file in DB2/400. However, I try to avoid 'busy flags' because in the event of an abnormal termination you can end up with the flag in the wrong state.

 

[arst06d]

Hi once more
The completion status is written to records in the AS400 tables. DB0mrrsm' s suggestion would be great if the connection for the ExecuteSQLTask was sql server. However, the AS400 does not support the WAITFOR command, nor does it have a similar construct in SQL400.
as I say, I did try having a series of tasks in the DTS package
-ExecuteSQL Task connected to sql server to do a waitfor 1am
-ExecuteSQL task connected to AS400 to check the flag & set a global variable
-ActiveX task to check the value of the globalvariable - if not complete, set the status of the AS400 check back to waiting

Couldn't get it to work.

 

[leveyp]

Just had another thought.
Instead of adding a record to act as a completed flag you could delete the DB2400 file at the start of processing then create it (empty is fine) on completion. Then in the DTS job you could use a bit of VB script to check of the presence of the file...

'Connect
Set cn = CreateObject("ADODB.Connection")
cn.Open "DSN=RCHAS194;" ',"user", "pwd"

'Get some basic metadata on the AS/400 file.
' Could use Set md = cn.OpenSchema(adSchemaColumns, Array(vEmpty, Lib, FileName))
Set md = cn.Execute( _
"SELECT COLUMN_COUNT from QSYS2.SYSTABLES WHERE TABLE_NAME = '" & _
Trim(TableName) & "' AND TABLE_OWNER = '" & Trim(Lib) & "' ")
If md.EOF Then
'File does not exist.

End If

 

[yksvaan]

It is possible to make a 'feedback' loop, can't guess the reason why you couldn't get it to work.

One solution would be if would define AS400 as linked server in SQL, so you could write the loop in Execute SQL Task on sql2000. But I'm not that familiar with linked servers.

As it was stated earlier, another suggestion would be to write the loop in vbscript, package would go
-Execute SQL on sql200 WAITFOR TIME to set the start time
-in ActiveX vbscript
-check status from AS400 ODBC connection with ADO
recordset (or with some kind of connection)
- if status not ok make it wait for some time (sleep)
Transform Data ...

The problem is there's no sleep function in vbscript, and you can't use WScript.Sleep function. In the old days we would have coded the functionality ourselves . I wrote the sleep function below in vbscript, BUT it pins cpu, but I don't know if this has any significance to the overall performance. I'm not specialized in vbscript, so don't take this as a textbook example. Actually there's the ADO part too, but to a different vendor's database.

' Private Function mySnore
' returns seconds lost or -1 for invalid use
' it will pin cpu
Private Function mySnore(secs)

' this will limit to 1 hour to prevent mistakes, you can change this to suit yourself
If secs < 3600 Then

inMyTime = Now()
inYourTime = Now()
While DateDiff( "s", inMyTime, inYourTime ) < secs
inYourTime = Now()
Wend
mySnoreRet = DateDiff( "s", inMyTime, inYourTime )

Else
mySnoreRet = -1
End If

mySnore = mySnoreRet

End Function

Function Main()
dim myConn
dim myRecordset
dim iRowField
dim iRowFlag

' instantiate the ADO objects
set myConn = CreateObject("ADODB.Connection")
set myRecordset = CreateObject("ADODB.Recordset")

' set the connection properties to point to the SCOTT database,
' using the EMP table
' Provider: oledb for ODBC connections
myConn.Open = "Provider=MSDASQL.1;Data Source=SCOTT;User ID=scott;Password=tiger"
' write your own connection string for DB2

mySQLCmdText = "SELECT COUNT(*) AS ROWCOUNT FROM EMP"

Do

mySnore(900) ' 15 minutes

myRecordset.Open mySQLCmdText, myConn
set Flds = myRecordset.Fields
set iRowField = Flds("ROWCOUNT")
iRowFlag=iRowField.Value
myRecordset.Close

Loop Until iRowFlag = 14

Set myRecordset = Nothing
Set myConn = Nothing
msgbox Time
Main = DTSTaskExecResult_Success
End Function

Cheers

 

[yksvaan]

It is possible to make a 'feedback' loop, can't guess the reason why you couldn't get it to work.

One solution would be if would define AS400 as linked server in SQL, so you could write the loop in Execute SQL Task on sql2000. But I'm not that familiar with linked servers.

As it was stated earlier, another suggestion would be to write the loop in vbscript, package would go
-Execute SQL on sql200 WAITFOR TIME to set the start time
-in ActiveX vbscript
-check status from AS400 ODBC connection with ADO
recordset (or with some kind of connection)
- if status not ok make it wait for some time (sleep)
Transform Data ...

The problem is there's no sleep function in vbscript, and you can't use WScript.Sleep function. In the old days we would have coded the functionality ourselves . I wrote the sleep function below in vbscript, BUT it pins cpu, but I don't know if this has any significance to the overall performance. I'm not specialized in vbscript, so don't take this as a textbook case.. Actually there's the ADO part too, but to a different vendor's database.

' Private Function mySnore
' returns seconds lost or -1 for invalid use
' it will pin cpu
Private Function mySnore(secs)

' this will limit to 1 hour to prevent mistakes, you can change this to suit yourself
If secs < 3600 Then

inMyTime = Now()
inYourTime = Now()
While DateDiff( "s", inMyTime, inYourTime ) < secs
inYourTime = Now()
Wend
mySnoreRet = DateDiff( "s", inMyTime, inYourTime )

Else
mySnoreRet = -1
End If

mySnore = mySnoreRet

End Function

Function Main()
dim myConn
dim myRecordset
dim iRowField
dim iRowFlag

' instantiate the ADO objects
set myConn = CreateObject("ADODB.Connection")
set myRecordset = CreateObject("ADODB.Recordset")

' set the connection properties to point to the SCOTT database,
' using the EMP table
' Provider: oledb for ODBC connections
myConn.Open = "Provider=MSDASQL.1;Data Source=SCOTT;User ID=scott;Password=tiger"
' write your own connection string for DB2

mySQLCmdText = "SELECT COUNT(*) AS ROWCOUNT FROM EMP"

Do

mySnore(900) ' 15 minutes

myRecordset.Open mySQLCmdText, myConn
set Flds = myRecordset.Fields
set iRowField = Flds("ROWCOUNT")
iRowFlag=iRowField.Value
myRecordset.Close

Loop Until iRowFlag = 14

Set myRecordset = Nothing
Set myConn = Nothing

Main = DTSTaskExecResult_Success
End Function

Cheers

 

[yksvaan]

Apologies for double-posting. Something wrong with lines..

 

[yksvaan]

Maybe a better way to take a nap would be to have a connection to sqlserver and use the waitfor command. Something like this:

dim myConn
dim myRecordset

' instantiate the ADO objects
set myConn = CreateObject("ADODB.Connection")
set myRecordset = CreateObject("ADODB.Recordset")

' set the connection properties
' Provider: oledb for sqlserver
myConn.Open = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial Catalog=pubs;Data Source=myservername"

mySQLCmdText = "WAITFOR DELAY '00:00:10' SELECT @@ERROR"

myRecordset.Open mySQLCmdText, myConn

myRecordset.Close

Cheers