Terminal Monitor Macro Examples
1. Terminal Monitor will "sleep" for 3 seconds:
\shell sleep 3
2. Terminal Monitor will execute commands inside xyz.sh
\shell /home/ingres/user01/xyz.sh
or
\shell ./xyz.sh
For example if xyz.sh contains:
echo
echo hello john
echo
sql -S foo <<EOF
SELECT ASCII(COUNT(*)) FROM iitables\g
EOF
echo
exit
This will return:
*\sh ./xyz.sh
hello john
147
continue
3. A simple branch:
\branch ?{ifsame;@{read Enter data:};a;1;0}=1 valueok
The {read Enter data:} writes "Enter data:" on the terminal and accepts input from the terminal.
The {read Enter data:} macro is preceded by an @ because the parameter must be pre-scanned.
What is typed in response to {read Enter data:} becomes the first parameter in the {ifsame;@{read Enter data:};a;1;0} macro.
The {ifsame;@{read Enter data:};a;1;0} macro compares the first parameter to the second parameter which is the character "a".
If "a" is entered on the terminal, {ifsame; ...} evaluates to 1, the third parameter.
If anything other than "a" is entered, {ifsame; ...} evaluates to 0, the forth parameter.
If {ifsame; ...} is equal to 1, the code branches to the label ''valueok''.
4. A branching loop:
\macro
\mark noindex
SELECT CHAR(table_name,32) FROM iitables WHERE table_reltid = RANDOM(180,189) \g
/* If the row count is greater than 1, then the table has an index branch to "end"*/
/* Otherwise try again by branching to "retry"*/
\branch ?{ifgt;@{tuplecount};1;0;1}=1 retry
\branch end
\mark retry
{type table has no index}\v
\branch noindex
\mark end
\q
5. An example of prescan:
{define;typeit $$s;{type $s}}\eval
{define;line;this is text}\eval
typeit line\eval
Gives:
line
However, the entry:
typeit @line\eval
Gives:
this is text
Another example:
\macro
{define;x;@{tuplecount}}\eval
SELECT * FROM iitables WHERE table_name LIKE ‘%index%’\g
This will set x to 10:
{type @x}\eval
{define;y;@{tuplecount}}\eval
SELECT count(*) FROM iitables WHERE table_name LIKE ‘%index%’\g
This will set y to 1:
{type @y}\eval
If the "x" and "y" are not macro processed, as in the following:
{ifeq;x;y;{type @x equal to y};{type @x not equal y }}\eval
the preceding evaluates to: 1 equal to 10, which is an incorrect evaluation.
If the "x" and "y" are macro processed as in the following:
{ifeq;@x;@y;{type @x equal to y};{type @x not equal y }}\eval
the preceding evaluates to: 1 not equal to 10, which is a correct evaluation.
6. Prints "<number of rows> tuples touched" by a query after the query has executed:
\macro
{define;{begintrap};{remove;{tuplecount}}}\eval
{define;{continuetrap}; \
{ifeq;@{tuplecount};{tuplecount};; \
{type @{tuplecount} tuples touched}}}\eval
SELECT COUNT(*) FROM iitables\p\g
Gives:
* \macro
* {define;{begintrap};{remove;{tuplecount}}}\eval
continue
* {define;{continuetrap}; \
* {ifeq;@{tuplecount};{tuplecount};; \
* {type @{tuplecount} tuples touched}}}\eval
continue
* SELECT COUNT(*) FROM iitables\g
Executing . . .
+-------------+
|col1 |
+-------------+
| 147|
+-------------+
1 tuples touched
7. Finds the SQL statement error number:
* \macro
* SELECT * FROM foo\go
Executing . . .
E_US0845 Table 'foo' does not exist or is not owned by you.
(Mon May 28 14:04:00 2012)
continue
* {type @{errornumber}}\eval
30100
The value returned is the Generic Error Code.
8. Uses {errornumber} to detect deadlock and to retry.
Tables test and test2 are being updated in a different order by two different sessions. This is a classic case that can cause a deadlock.
Step 1: Create two tables:
CREATE TABLE test (col1 INTEGER NOT NULL)\go
INSERT INTO test VALUES (1)\go
CREATE TABLE test2 (col1 INTEGER NOT NULL)\go
Step 2: Create file deadlock_test1.sql containing the following:
* SQL script deadlock_test1.sql to show use of {errornumber} macro */
\macro
/* need a place to jump back to */
\mark an insert
INSERT INTO test SELECT * FROM test\p\g
/* To guarantee a deadlock */
\shell sleep 5
SELECT count(*) FROM test2\p\g
/* if generic error = 49900 then go to retry label */
/* The equivalent dbmserror is 4700 /
\branch ?{ifeq;@{errornumber};49900;1;0}=1 retry
/* if no deadlock go to the label end */
\branch end
/* Offer chance to retry */
\mark retry
\branch ?{ifsame;@{read Deadlock would you like to Retry (y/n)};y;1;0}=1 aninsert
\mark end
COMMIT\p\g
Step 3: Create file deadlock_test2.sql containing the following:
/* SQL script deadlock_test2.sql to show use of {errornumber} macro */
\macro
/* need a place to jump back to */
\mark anupdate
UPDATE test2 SET col1=10\p\g
/* To guarantee a deadlock */
\shell sleep 5
SELECT count(*) FROM test\p\g
/* if generic error = 49900 then go to retry label
/* The equivalent dbmserror is 4700 /
\branch ?{ifeq;@{errornumber};49900;1;0}=1 retry
/* if no deadlock go to the label end */
\branch end
/* Offer user choice to retry */
\mark retry
\branch ?{ifsame;@{read Deadlock would you like to Retry (y/n)};y;1;0}=1 anupdate
\mark end
commit\p\g
Step 4: In session one run:
sql mydb
* \i deadlock_test1.sql
Note: Do not execute the include file yet.
Step 5: In session two run:
sql mydb
* \i deadlock_test2.sql
Note: Do not execute the include file yet.
Step 6: Execute the \include file by pressing Enter on each session.
One of the sessions will hit the deadlock and you will be offered the chance to retry the query. Answer "y" to retry.