Antwort: RE: [toad_db2] Re: Re: Automation script and variable bound to export SQL [1 Attachment]

Guest · March 15, 2010, 8:31am

Antwort: RE: [toad_db2] Re: Re: Automation script and variable bound to
export SQL [1 Attachment]

DocJones · March 15, 2010, 8:31am

Hello Debbie,

I fixed the DATE/TIME issue in a similar
way. This issue can be considered “solved” for me

As for the VARCHAR: i got the right
quoting to work for my queries using your “trick”. Thanks for
the heads up.

While i ran my script, i discovered
the following in my log:

Automation Logging:15.03.2010 13:47:30: Executing
activity Loop_row_2 using row 1

Automation Logging:15.03.2010 13:47:30: Variable
“EMAIL” set to “USR.USR_EMAIL1”

Automation Logging:15.03.2010 13:47:30: Variable
value = Ungültiges Zeichen

Although the script ran fine.

The Workflow editor shows the dreaded
red exclamation mark attached to the corresponding “Set Variable”
activity:

I attached the TAS file again for you
to have a peek at

Regarding the “PAUSE”/“SLEEP”:
I can think of use cases in which you have to wait for a certain record
to appear in a given table.

This can be done via polling a table
for that given record until a found criteria is met. (Its is possible to
use a “Connection” activity inside a WHILE loop) To reduce database
scamming in WHILE loops, a “sleep(1)” element to sleep 1 second
may be useful. (Imagine a script facilitating parallel activities with
one branch calculating an value and another branch waiting for that value
to export some data).

thanks and regards

Marc

IDS Logistik GmbH

Marc Rink

Software Development Manager -

Saaläckerstrasse 8

63801 Kleinostheim

Tel: +49(6027)40903-13

Fax: +49(6027)40903-9013

MOB: +49(170)9672528

Internet: http://www.ids-logistik.de

E-Mail: marc_rink@ids-logistik.de

Sitz der Gesellschaft: Kleinostheim

Amtsgericht Aschaffenburg - HRB 10253

Geschäftsführer: Dr. Michael Bargl

Vorsitzender des Beirates: Mathias Krage

“Computer games don’t affect kids. I mean if Pac-Man affected us as
kids, we’d all be running around in darkened rooms, munching magic pills
and listening to repetitive electronic music.”

–Kristian Wilson, Nintendo 1989.

Von:
Debbie Peabody dpeabody@quest.com

An:
"toad_db2@yahoogroups.com"
toad_db2@yahoogroups.com, “toaddb2@questmail.info” toaddb2@questmail.info

Datum:
13.03.2010 01:08

Betreff:
RE: [toad_db2] Re: Re: Automation script
and variable bound to export SQL [1 Attachment]

Gesendet von:
toad_db2@yahoogroups.com

[Attachment(s)
from Debbie Peabody included below]
Hi Marc,

I was able to look into this
a bit further. I have built two test scripts to cover these two items and
they work for me. Of course I made two very simple sample tables.

Date/Time range:

For my test I created a variable named MYMON
and set to numerical value 0. Note: There are really only two types of
Automation variables, strings and numbers. The type of variable is
determined by what the initial value is. To make a var a string I usually
set it to EMPTY.

In my test I got the current month with the
sql below. I used your idea of casting the month to a number

select
CAST(MONTH(CURRENT
DATE) as
INT) MON from
SYSIBM.sysdummy1;

To set the variable MYMON to the value returned
in the above SQL you have to put it in a loop dataset (seems silly but
I wasn’t thinking of this scenario. See sample code).

Once I have my value set I can use that in
a bind to get only the rows that are in that month. Sample SQL below.

SELECT
*

FROM
DPEABODY.DEBEMP

WHERE
MONTH(HIREDATE)

:MYMON;

Keeping Leading Zeros on VarChar Column

Since I want a string automation variable
I create one named DEPTNOC and set initial value to EMPTY.

The next step is to get the driving list
of department numbers. To work around the current limitation of trimming
the leading zeros I am using a SQL that will give me a quoted string value.
When a value is quoted the automation variable activity will leave it alone.

select
‘"’||DEPTNOC||’"’
DEPTNOC from
DPEABODY.DEPT;

Now I can set my value and use in SQL statement
with bind var.

SELECT
EMPNO, DEPTNOC, NAME

FROM
DPEABODY.DEBEMP

WHERE
DEPTNOC =
:DEPTNOC

I have attached test scripts including
DDL for the two tables.

RE Enhancement List:

The first two items I have already
on my list to add. I love your suggestions regarding the print function.

I need more info on the pause
item. I need more details on the use case.

RE: Parallel activities. Using
this activity for organizing wasn’t it’s original purpose however I can
see how it is handy for this. I’ll look into working as children of a
connection. (Or other ways of making the organization easier)

Hope these will get you farther with your
script.

Debbie

From: toad_db2@yahoogroups.com [mailto:toad_db2@yahoogroups.com]
On Behalf Of marc_rink@ids-logistik.de**
Sent:** Friday, March 12, 2010 1:11 AM**
To:** toad_db2@yahoogroups.com; toaddb2@questmail.info**
Cc:** toad_db2@yahoogroups.com**
Subject:** [toad_db2] Re: Re: Automation script and variable bound to
export SQL [7 Attachments]

**[****Attachment(s)**from marc_rink@ids-logistik.de included below]

Hello Debbie,

**
Date/Time range:**

Actually i was trying to provide an easy way to set the requested month
of the export queries by a centralized variable. Every view has a “between
and <first day of current month-X+1>”
in its where clause and by setting X i can easily export different months.
Or at least thats what i have tried

I played around a bit and came up with the following solution:

“… between qu.trunc(current timestamp-cast(:MONTH as INTEGER) month,‘MM’)
and qu.trunc(current timestamp-cast(:MONTH as INTEGER) month+1 month,‘MM’)
with ur;”

with qu.trunc(…) is a supporting UDF truncating a TIMESTAMP to the 2nd
Parameter (‘MM’ is month).

Now i just need a “Input variable value here” dialog box and
i am fine

**
VARCHAR**

I tried to quote all occurencies of :DEPOT, but the automation script does
not work properly (as you might see from the attached log file). I added
all called scripts and the TAS file as well to help with understanding.
You may have to trim the pathnames in it a bit.

**
Suggestions/Features requested:**

Its a bit clumsy to create a SQL script for each export ans save a seperate
SQL file. Would be easier if you could choose if you want to add the SQL
to be executed directly to the Export activity (without creating a SQL
file).
Relative pathnames in ALL filename dialoges (Execute Script, Select to
File, Attach emails, etc…)
Print function with detailed information of each activity attached (just
like the current tooltip-bubbles onMouseOver event).
add a “Execute STP” activity (currently i have to create a
SQL script with “call .()”
in it and Execute it
for polling reason (and while loops add a “Sleep”/“Pause”
activity) (eg, i want to wait for a certain record in a table to occur)

**
Further Questions**

It seems i cant place parallel activities within a connection. This makes
huge scripts a bit clumsy and very inefficient in terms of connection re-using.

Just my 2ct

Hope you can help me with the VARCHAR issue

regards & keep up the great work

/Marc

IDS Logistik GmbH

Marc Rink

Software Development Manager -

Saaläckerstrasse 8

63801 Kleinostheim

Tel: +49(6027)40903-13

Fax: +49(6027)40903-9013

MOB: +49(170)9672528

Internet: http://www.ids-logistik.de

E-Mail: marc_rink@ids-logistik.de

Sitz der Gesellschaft: Kleinostheim

Amtsgericht Aschaffenburg - HRB 10253

Geschäftsführer: Dr. Michael Bargl

Vorsitzender des Beirates: Mathias Krage

“Computer games don’t affect kids. I mean if Pac-Man affected us as
kids, we’d all be running around in darkened rooms, munching magic pills
and listening to repetitive electronic music.”

–Kristian Wilson, Nintendo 1989.

Von:

Debbie Peabody dpeabody@quest.com

An:
"toad_db2@yahoogroups.com" toad_db2@yahoogroups.com

Datum:

11.03.2010 22:52

Betreff:

RE: [toad_db2] Re: Automation script and
variable bound to export SQL [1 Attachment]

Gesendet von:

toad_db2@yahoogroups.com

[Attachment(s)
from Debbie Peabody included below]

On the date range issue. It looks like you
want the last 30 days. I would send your SQL to the Query Builder and use
the Date Range tab and let it build the filter for the last 30 days. This
is a built in bind feature. See attached. You would not need to use
a bind var at all for this.

On the varchar with preceding zeros. The
evaluation code seems to be stripping it off. I don’t know DB2 very well.
What I would try is to surround the value in quotes in my SQL statement.
The code will look for the quotes and leave the value alone. But this would
need to be done in the SQL and not after the value is retrieved.

Debbie

From: toad_db2@yahoogroups.com [mailto:toad_db2@yahoogroups.com]
On Behalf Of Marc Rink**
Sent:** Thursday, March 11, 2010 7:26 AM**
To:** toad_db2@yahoogroups.com**
Subject:** [toad_db2] Re: Automation script and variable bound to export
SQL

Message from: DocJones

Heyas,

another thing: i have retrieved values in the form (VARCHAR(10)):
‘0100’, ‘0500’, … and assigned them to a Variable. But the Variable is
being set to the integer representation of the required value: 100, 500,…

Those numbers are branch office numbers and must be with leading zeroes.
How to achive this?

regards

/Z

Historical Messages

Author: Marc Rink

Date: Thu Mar 11 07:26:06 PST 2010

Heyas,

another thing: i have retrieved values in the form (VARCHAR(10)):
‘0100’, ‘0500’, … and assigned them to a Variable. But the Variable is
being set to the integer representation of the required value: 100, 500,…

Those numbers are branch office numbers and must be with leading zeroes.
How to achive this?

regards

/Z

__

Author: Marc Rink

Date: Thu Mar 11 01:03:55 PST 2010

Heyas,

i have an automation script that exports several VIEWS with a given MONTH
Parameter. MONTH is a Variable in the script evaluating to the date last
month (DateAdd(“m”,-1,Now)) used in the “where” clause
for a “between…”.

It seems i cant bind the MONTH variable to the Select to File activitiy.

Here is the log of the automation script:

Category Timestamp Message

Automation Logging 11.03.2010 09:58:02
Build started

Automation Logging 11.03.2010 09:58:03
Warning: No Exception Handler. To send an Email when
there is an error, change the value of Email on Error to true and enter
email values.

Automation Logging 11.03.2010 09:58:03
Build Completed

Automation Logging 11.03.2010 09:58:03
Variable “MONTH” set to “0”

Automation Logging 11.03.2010 09:58:03
Variable value = 0

Automation Logging 11.03.2010 09:58:03
Variable “MONTH” set to “DateAdd(“m”,-1,
Now)”

Automation Logging 11.03.2010 09:58:03
Variable value = 11.02.2010 09:58:03

Automation Logging 11.03.2010 09:58:03
Connecting to LOGI_LS (ELIX)

Automation Logging 11.03.2010 09:58:03
Connection successful.

Automation Logging 11.03.2010 09:58:03
Begin Export

Automation Logging 11.03.2010 09:58:04
SQL to execute: select * from elix.sdgaccess
where upper(vername1) like ‘FEDERAL%’ and upper(vername1) like ‘%MOGUL%’
and (verland!=‘DE’ or empland!=‘DE’) and borddat between qu.trunc(:MONTH-1
month,‘MM’) and qu.trunc(:MONTH,‘MM’)with ur;

Automation Logging 11.03.2010 09:58:04
Beginning export.

Statusbar 11.03.2010 09:58:05
Export Finished [11], errors detected, click
for summary.

Automation Logging 11.03.2010 09:58:05
ERROR [42610] [IBM][DB2/6000] SQL0418N A statement
contains a use of an untyped parameter marker or a null value that is not
valid. SQLSTATE=42610

ERROR [22005] [IBM] CLI0112E Error in assignment. SQLSTATE=22005

Automation Logging 11.03.2010 09:58:05
bei Quest.Toad.Workflow.Activities.Database.SelectToExcelActivity.Execute(ActivityExecutionContext
executionContext)

Automation Logging 11.03.2010 09:58:05
Failed

and here is the Exception raised from the export activitiy:

Thread (11) Export Started [11.03.2010 09:58:04]

Thread (11) Export Wizard: Building object list

Thread (11) Exporting Data

Thread (11) Object SQL Query started.

Thread (47) InternalReadBackground - start

Thread (47) InternalReadBackground - exception ERROR [42610] [IBM][DB2/6000]
SQL0418N A statement contains a use of an untyped parameter marker
or a null value that is not valid. SQLSTATE=42610

ERROR [22005] [IBM] CLI0112E Error in assignment. SQLSTATE=22005

Thread (47) InternalReadBackground - stack bei IBM.Data.DB2.DB2Command.ExecuteReaderObject(CommandBehavior
behavior, String method, DB2CursorType reqCursorType, Boolean abortOnOptValueChg,
Boolean skipDeleted, Boolean isResultSet, Int32 maxRows, Boolean skipInitialValidation)

bei IBM.Data.DB2.DB2Command.ExecuteReaderObject(CommandBehavior
behavior, String method)

bei IBM.Data.DB2.DB2Command.ExecuteReader(CommandBehavior behavior)

bei IBM.Data.DB2.DB2Command.ExecuteDbDataReader(CommandBehavior
behavior)

bei System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()

bei Quest.Toad.Db.ToadDataAdapter.InternalReadBackground()

Thread (17) DoneReadTreadWriter

Thread (17) DoneReadThreadWriter finally - adapter.RowsRead 0

Thread (11) Object SQL Query finished with error: ERROR [42610] [IBM][DB2/6000]
SQL0418N A statement contains a use of an untyped parameter marker
or a null value that is not valid. SQLSTATE=42610

ERROR [22005] [IBM] CLI0112E Error in assignment. SQLSTATE=22005

Thread (11) bei IBM.Data.DB2.DB2Command.ExecuteReaderObject(CommandBehavior
behavior, String method, DB2CursorType reqCursorType, Boolean abortOnOptValueChg,
Boolean skipDeleted, Boolean isResultSet, Int32 maxRows, Boolean skipInitialValidation)

bei IBM.Data.DB2.DB2Command.ExecuteReaderObject(CommandBehavior
behavior, String method)

bei IBM.Data.DB2.DB2Command.ExecuteReader(CommandBehavior behavior)

bei IBM.Data.DB2.DB2Command.ExecuteDbDataReader(CommandBehavior
behavior)

bei System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()

bei Quest.Toad.Db.ToadDataAdapter.InternalReadBackground()

Thread (11) Export Finished [11.03.2010 09:58:05]

Do i have to explicitly cast the MONTH Variable to a TIMESTAMP? If so,
how is this achivied best?

regards

/Marc

IDS Logistik GmbH

Marc Rink

Software Development Manager -

Saaläckerstrasse 8

63801 Kleinostheim

Tel: +49(6027)40903-13

Fax: +49(6027)40903-9013

MOB: +49(170)9672528

Internet: http://www.ids-logistik.de

E-Mail: marc_rink@ids-logistik.de

Sitz der Gesellschaft: Kleinostheim

Amtsgericht Aschaffenburg - HRB 10253

Geschäftsführer: Dr. Michael Bargl

Vorsitzender des Beirates: Mathias Krage

“Computer games don’t affect kids. I mean if Pac-Man affected us as
kids, we’d all be running around in darkened rooms, munching magic pills
and listening to repetitive electronic music.”

–Kristian Wilson, Nintendo 1989.

__

.,._

Attachment(s) from Debbie
Peabody

1 of 1 File(s)

SamplesForMarc.zip

Reply
to sender | Reply
to group | Reply
via web post | Start
a New Topic

Messages
in this topic (5)

Recent Activity:

Visit
Your Group

Switch to: Text-Only,
Daily
Digest • Unsubscribe
• Terms
of Use

.

,.,_
att1.dat (50.8 KB)

DocJones · March 15, 2010, 8:31am

Hello Debbie,

I fixed the DATE/TIME issue in a similar
way. This issue can be considered “solved” for me

As for the VARCHAR: i got the right
quoting to work for my queries using your “trick”. Thanks for
the heads up.

While i ran my script, i discovered
the following in my log:

Automation Logging:15.03.2010 13:47:30: Executing
activity Loop_row_2 using row 1

Automation Logging:15.03.2010 13:47:30: Variable
“EMAIL” set to “USR.USR_EMAIL1”

Automation Logging:15.03.2010 13:47:30: Variable
value = Ungültiges Zeichen

Although the script ran fine.

The Workflow editor shows the dreaded
red exclamation mark attached to the corresponding “Set Variable”
activity:

I attached the TAS file again for you
to have a peek at

Regarding the “PAUSE”/“SLEEP”:
I can think of use cases in which you have to wait for a certain record
to appear in a given table.

This can be done via polling a table
for that given record until a found criteria is met. (Its is possible to
use a “Connection” activity inside a WHILE loop) To reduce database
scamming in WHILE loops, a “sleep(1)” element to sleep 1 second
may be useful. (Imagine a script facilitating parallel activities with
one branch calculating an value and another branch waiting for that value
to export some data).

thanks and regards

Marc

IDS Logistik GmbH

Marc Rink

Software Development Manager -

Saaläckerstrasse 8

63801 Kleinostheim

Tel: +49(6027)40903-13

Fax: +49(6027)40903-9013

MOB: +49(170)9672528

Internet: http://www.ids-logistik.de

E-Mail: marc_rink@ids-logistik.de

Sitz der Gesellschaft: Kleinostheim

Amtsgericht Aschaffenburg - HRB 10253

Geschäftsführer: Dr. Michael Bargl

Vorsitzender des Beirates: Mathias Krage

“Computer games don’t affect kids. I mean if Pac-Man affected us as
kids, we’d all be running around in darkened rooms, munching magic pills
and listening to repetitive electronic music.”

–Kristian Wilson, Nintendo 1989.

Von:
Debbie Peabody dpeabody@quest.com

An:
"toad_db2@yahoogroups.com"
toad_db2@yahoogroups.com, “toaddb2@questmail.info” toaddb2@questmail.info

Datum:
13.03.2010 01:08

Betreff:
RE: [toad_db2] Re: Re: Automation script
and variable bound to export SQL [1 Attachment]

Gesendet von:
toad_db2@yahoogroups.com

[Attachment(s)
from Debbie Peabody included below]
Hi Marc,

I was able to look into this
a bit further. I have built two test scripts to cover these two items and
they work for me. Of course I made two very simple sample tables.

Date/Time range:

For my test I created a variable named MYMON
and set to numerical value 0. Note: There are really only two types of
Automation variables, strings and numbers. The type of variable is
determined by what the initial value is. To make a var a string I usually
set it to EMPTY.

In my test I got the current month with the
sql below. I used your idea of casting the month to a number

select
CAST(MONTH(CURRENT
DATE) as
INT) MON from
SYSIBM.sysdummy1;

To set the variable MYMON to the value returned
in the above SQL you have to put it in a loop dataset (seems silly but
I wasn’t thinking of this scenario. See sample code).

Once I have my value set I can use that in
a bind to get only the rows that are in that month. Sample SQL below.

SELECT
*

FROM
DPEABODY.DEBEMP

WHERE
MONTH(HIREDATE)

:MYMON;

Keeping Leading Zeros on VarChar Column

Since I want a string automation variable
I create one named DEPTNOC and set initial value to EMPTY.

The next step is to get the driving list
of department numbers. To work around the current limitation of trimming
the leading zeros I am using a SQL that will give me a quoted string value.
When a value is quoted the automation variable activity will leave it alone.

select
‘"’||DEPTNOC||’"’
DEPTNOC from
DPEABODY.DEPT;

Now I can set my value and use in SQL statement
with bind var.

SELECT
EMPNO, DEPTNOC, NAME

FROM
DPEABODY.DEBEMP

WHERE
DEPTNOC =
:DEPTNOC

I have attached test scripts including
DDL for the two tables.

RE Enhancement List:

The first two items I have already
on my list to add. I love your suggestions regarding the print function.

I need more info on the pause
item. I need more details on the use case.

RE: Parallel activities. Using
this activity for organizing wasn’t it’s original purpose however I can
see how it is handy for this. I’ll look into working as children of a
connection. (Or other ways of making the organization easier)

Hope these will get you farther with your
script.

Debbie

From: toad_db2@yahoogroups.com [mailto:toad_db2@yahoogroups.com]
On Behalf Of marc_rink@ids-logistik.de**
Sent:** Friday, March 12, 2010 1:11 AM**
To:** toad_db2@yahoogroups.com; toaddb2@questmail.info**
Cc:** toad_db2@yahoogroups.com**
Subject:** [toad_db2] Re: Re: Automation script and variable bound to
export SQL [7 Attachments]

**[****Attachment(s)**from marc_rink@ids-logistik.de included below]

Hello Debbie,

**
Date/Time range:**

Actually i was trying to provide an easy way to set the requested month
of the export queries by a centralized variable. Every view has a “between
and <first day of current month-X+1>”
in its where clause and by setting X i can easily export different months.
Or at least thats what i have tried

I played around a bit and came up with the following solution:

“… between qu.trunc(current timestamp-cast(:MONTH as INTEGER) month,‘MM’)
and qu.trunc(current timestamp-cast(:MONTH as INTEGER) month+1 month,‘MM’)
with ur;”

with qu.trunc(…) is a supporting UDF truncating a TIMESTAMP to the 2nd
Parameter (‘MM’ is month).

Now i just need a “Input variable value here” dialog box and
i am fine

**
VARCHAR**

I tried to quote all occurencies of :DEPOT, but the automation script does
not work properly (as you might see from the attached log file). I added
all called scripts and the TAS file as well to help with understanding.
You may have to trim the pathnames in it a bit.

**
Suggestions/Features requested:**

Its a bit clumsy to create a SQL script for each export ans save a seperate
SQL file. Would be easier if you could choose if you want to add the SQL
to be executed directly to the Export activity (without creating a SQL
file).
Relative pathnames in ALL filename dialoges (Execute Script, Select to
File, Attach emails, etc…)
Print function with detailed information of each activity attached (just
like the current tooltip-bubbles onMouseOver event).
add a “Execute STP” activity (currently i have to create a
SQL script with “call .()”
in it and Execute it
for polling reason (and while loops add a “Sleep”/“Pause”
activity) (eg, i want to wait for a certain record in a table to occur)

**
Further Questions**

It seems i cant place parallel activities within a connection. This makes
huge scripts a bit clumsy and very inefficient in terms of connection re-using.

Just my 2ct

Hope you can help me with the VARCHAR issue

regards & keep up the great work

/Marc

IDS Logistik GmbH

Marc Rink

Software Development Manager -