Antwort: RE: Antwort: RE: [toad_db2] Re: Re: Automation script and variable
bound to export SQL
Hi Debbie,
Strange: Using the # to set a variable
to a resultsets column is nowehere mentioned in the documentation. And
somehow i managed to get it run without them. I added the #'s and it still
runs fine
I’d like to mention that on the “Value”
dialog of the “Set Variable” activity doesn’t offer the selection
of Resultsets (and their column list is missing as well). This may be hard
to achieve (dynamically retrieve the results of a given script), but may
be a well needed feature.
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
Kopie:
“toaddb2@questmail.info” toaddb2@questmail.info
Datum:
16.03.2010 02:03
Betreff:
RE: Antwort: RE: [toad_db2] Re: Re:
Automation script and variable bound to export SQL
Gesendet von:
toad_db2@yahoogroups.com
Your Set_Email activity does not appear to
be dereferencing the table column. You need to use the # symbol to get
the content of table column.
You need to set the value to #DEPOTLISTE.DEPOTID#
and #USR.USR_EMAIL1#
I like to add a Comment activity right after
I set a variable from a table column. This way I can confirm in the log
that the value got set correctly. Something like à
Value of DEPOTID = #DEPOTID#
Debbie
From: toad_db2@yahoogroups.com [mailto:toad_db2@yahoogroups.com]
On Behalf Of marc_rink@ids-logistik.de**
Sent:** Monday, March 15, 2010 6:31 AM**
To:** toad_db2@yahoogroups.com**
Cc:** toaddb2@questmail.info; toad_db2@yahoogroups.com**
Subject:** Antwort: RE: [toad_db2] Re: Re: Automation script and variable
bound to export SQL [1 Attachment]
**[****Attachment(s)**from marc_rink@ids-logistik.de included below]
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:
[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:
[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.
att1.dat (50.8 KB)
Hi Debbie,
Strange: Using the # to set a variable
to a resultsets column is nowehere mentioned in the documentation. And
somehow i managed to get it run without them. I added the #'s and it still
runs fine
I’d like to mention that on the “Value”
dialog of the “Set Variable” activity doesn’t offer the selection
of Resultsets (and their column list is missing as well). This may be hard
to achieve (dynamically retrieve the results of a given script), but may
be a well needed feature.
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
Kopie:
“toaddb2@questmail.info” toaddb2@questmail.info
Datum:
16.03.2010 02:03
Betreff:
RE: Antwort: RE: [toad_db2] Re: Re:
Automation script and variable bound to export SQL
Gesendet von:
toad_db2@yahoogroups.com
Your Set_Email activity does not appear to
be dereferencing the table column. You need to use the # symbol to get
the content of table column.
You need to set the value to #DEPOTLISTE.DEPOTID#
and #USR.USR_EMAIL1#
I like to add a Comment activity right after
I set a variable from a table column. This way I can confirm in the log
that the value got set correctly. Something like à
Value of DEPOTID = #DEPOTID#
Debbie
From: toad_db2@yahoogroups.com [mailto:toad_db2@yahoogroups.com]
On Behalf Of marc_rink@ids-logistik.de**
Sent:** Monday, March 15, 2010 6:31 AM**
To:** toad_db2@yahoogroups.com**
Cc:** toaddb2@questmail.info; toad_db2@yahoogroups.com**
Subject:** Antwort: RE: [toad_db2] Re: Re: Automation script and variable
bound to export SQL [1 Attachment]
**[****Attachment(s)**from marc_rink@ids-logistik.de included below]
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:
[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:
[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.