I set up TPoX environment on DB2 9 with NSE (Net Search Extender).
I want to query a full text search with a parameter marker by WorkloadDriver, but I can not.
NSE full text search query:
select * from security
where (contains(sdoc, '(sections("/Security/SecurityInformation/Description/BusinessSummary/Risk") "|1")')=1)%
Although I expect that "|1" is replaced with a word in the list (security_desc_jtext.txt),
the string "|1" is used as a search word.
I can not use ? in stead of "|1".
Because of a spec of NSE, a parameter marker can not be used. Is there any workaround?
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
1. Setup TPoX
2. Recreate security table with primary key (for NSE)
create table security (id bigint not null generated always as identity (start with 1, increment by 1, nocache) primary key, sdoc xml) in ts01 index in ts01ind;
3. Insert XMLs
4. Create and update a text index.
db2text "CREATE INDEX TXTIDX_SECURITY_DEF FOR TEXT ON security("SDOC") CCSID 1208 LANGUAGE JA_JP FORMAT XML UPDATE FREQUENCY NONE INDEX DIRECTORY /data/text CONNECT TO TPOX"
db2text "UPDATE INDEX TXTIDX_SECURITY_DEF FOR TEXT CONNECT TO TPOX"
5. Make a search word list
WorkloadDriver/input/security_desc_jtext.txt
In my environment, this txt file contains some Japanese word. This problem may not depend on a language.
6. Make a property file as follows
------------------------------------------------------------------
numOfTransactions = 1
7. Make a query file as follows
------------------------------------------------------------------
select * from security
where (contains(sdoc, '(sections("/Security/SecurityInformation/Description/BusinessSummary/Risk") "|1")')=1)%
------------------------------------------------------------------
DB2/queries_nse/get_security_by_nse_sqlxml.xqr
8. Run the query
$JAVADIR/java -classpath $CLASSPATH WorkloadDriver -d tpox -w properties/nsequery.properties -u 1 -tr 1 -v 2
9. A snapshot for dynamic sql or a user1.txt in the output directory indicate that the string "|1" is searched.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Thanks for the steps. I installed Net search extender but was not able to reproduce the error - the first query that I'm seeing is:
-----
select * from security where (contains(sdoc, '(sections("/Security/SecurityInformation/Description/BusinessSummary/Risk") "Muni National Short")')=1)
-----
I think it's possible that the characters in the input file (security_desc_jtext.txt) may be causing problems. Could you send me a portion of the file (i.e. first few lines)? Also could you try and run with a different input file (i.e. security_sectors.txt) and check if the first query had it's marker replaced properly?
Thanks,
Mike
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
In 9. user1.txt indicate that the "|1" is replaced, but a snapshot for dynamic sql shows it is not replaced.
By the CLP, following statement return many rows.
select * from security
where (contains(sdoc, '(sections("/Security/SecurityInformation/Description/BusinessSummary/Risk") "the")')=1)
By the WorkloadDriver with input file which contains one rows "the", no rows returns.
If you do not use parameter maker in xqr file, user1.txt shows many results.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Sorry for the late reply. Mike is on vacation now. I'm continuing investigate this problem ...
This problem is related to how we process parameter marks in the workload file. Current version of TPoX support 2 type of parameter markers (PM): 1) the "|<num>" style and 2) the tradictional SQL "?" style. The workload driver replace only the first type of PM with actual values. The second type of PM will be passed to DB server.
For xquery, the first type (ie. "|<num>") of PM is used because DB2 doesn't allow any PMs in xquery. The workloadDriver will substitute the PM with actual value before it submits the statement as static statement to the DB server. It will repeat the substituting and submiting once for each actual value.
For SQL statement, the second type ("?") of PM is used. The WorkloadDriver will submit the statement with the PMs as dynamic statement (preparedStatement in java). It will execute the preparedStatement once for each actual value.
In our case, since the statement is not an xquery statement (a SELECT stmt), it is submitted to DB server as a preparedStatement. But, as you mentioned, the DB server doesn't allow PMs in NSE statement, so the preparedStatement is created with no parameter. The "paramenter marker" in it is simply treated as a regular character. That's why you saw the "|1" still in the statement in the snapshot.
At this moment, a way I can think of to work around this problem is to create a statement for each of the actual values, and give this bunch of statements to WorkloadDriver ...
We're working on modifying the code to solve this problem!
Thank you for using TPoX!
Kevin Xie
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hi,
I set up TPoX environment on DB2 9 with NSE (Net Search Extender).
I want to query a full text search with a parameter marker by WorkloadDriver, but I can not.
NSE full text search query:
select * from security
where (contains(sdoc, '(sections("/Security/SecurityInformation/Description/BusinessSummary/Risk") "|1")')=1)%
related property file:
t8 = ../DB2/queries_nse/get_security_by_nse_sqlxml.xqr
p8|1 = file | ../WorkloadDriver/input/security_desc_jtext.txt
Although I expect that "|1" is replaced with a word in the list (security_desc_jtext.txt),
the string "|1" is used as a search word.
I can not use ? in stead of "|1".
Because of a spec of NSE, a parameter marker can not be used. Is there any workaround?
Hi Hidehiko,
Could you post the full contents of your properties file?
Does your property file contain transaction #1 (t1) or does it start from t8?
Other than that, what you are doing appears to be correct - I will try and duplicate the problem on our side.
Thanks,
Mike
Hi Mike,
Following is the procedure.
1. Setup TPoX
2. Recreate security table with primary key (for NSE)
create table security (id bigint not null generated always as identity (start with 1, increment by 1, nocache) primary key, sdoc xml) in ts01 index in ts01ind;
3. Insert XMLs
4. Create and update a text index.
db2text "CREATE INDEX TXTIDX_SECURITY_DEF FOR TEXT ON security("SDOC") CCSID 1208 LANGUAGE JA_JP FORMAT XML UPDATE FREQUENCY NONE INDEX DIRECTORY /data/text CONNECT TO TPOX"
db2text "UPDATE INDEX TXTIDX_SECURITY_DEF FOR TEXT CONNECT TO TPOX"
5. Make a search word list
WorkloadDriver/input/security_desc_jtext.txt
In my environment, this txt file contains some Japanese word. This problem may not depend on a language.
6. Make a property file as follows
------------------------------------------------------------------
numOfTransactions = 1
t1 = ../DB2/queries_nse/get_security_by_nse_sqlxml.xqr
p1|1 = file | ../WorkloadDriver/input/security_desc_jtext.txt
------------------------------------------------------------------
TPoX/WorkloadDriver/properties/nsequery.properties
7. Make a query file as follows
------------------------------------------------------------------
select * from security
where (contains(sdoc, '(sections("/Security/SecurityInformation/Description/BusinessSummary/Risk") "|1")')=1)%
------------------------------------------------------------------
DB2/queries_nse/get_security_by_nse_sqlxml.xqr
8. Run the query
$JAVADIR/java -classpath $CLASSPATH WorkloadDriver -d tpox -w properties/nsequery.properties -u 1 -tr 1 -v 2
9. A snapshot for dynamic sql or a user1.txt in the output directory indicate that the string "|1" is searched.
Hi Hidehiko,
Thanks for the steps. I installed Net search extender but was not able to reproduce the error - the first query that I'm seeing is:
-----
select * from security where (contains(sdoc, '(sections("/Security/SecurityInformation/Description/BusinessSummary/Risk") "Muni National Short")')=1)
-----
I think it's possible that the characters in the input file (security_desc_jtext.txt) may be causing problems. Could you send me a portion of the file (i.e. first few lines)? Also could you try and run with a different input file (i.e. security_sectors.txt) and check if the first query had it's marker replaced properly?
Thanks,
Mike
Hi Mike,
There is a mistake in my previous post.
In 9. user1.txt indicate that the "|1" is replaced, but a snapshot for dynamic sql shows it is not replaced.
By the CLP, following statement return many rows.
select * from security
where (contains(sdoc, '(sections("/Security/SecurityInformation/Description/BusinessSummary/Risk") "the")')=1)
By the WorkloadDriver with input file which contains one rows "the", no rows returns.
If you do not use parameter maker in xqr file, user1.txt shows many results.
Hi, Hidehiko,
Sorry for the late reply. Mike is on vacation now. I'm continuing investigate this problem ...
This problem is related to how we process parameter marks in the workload file. Current version of TPoX support 2 type of parameter markers (PM): 1) the "|<num>" style and 2) the tradictional SQL "?" style. The workload driver replace only the first type of PM with actual values. The second type of PM will be passed to DB server.
For xquery, the first type (ie. "|<num>") of PM is used because DB2 doesn't allow any PMs in xquery. The workloadDriver will substitute the PM with actual value before it submits the statement as static statement to the DB server. It will repeat the substituting and submiting once for each actual value.
For SQL statement, the second type ("?") of PM is used. The WorkloadDriver will submit the statement with the PMs as dynamic statement (preparedStatement in java). It will execute the preparedStatement once for each actual value.
In our case, since the statement is not an xquery statement (a SELECT stmt), it is submitted to DB server as a preparedStatement. But, as you mentioned, the DB server doesn't allow PMs in NSE statement, so the preparedStatement is created with no parameter. The "paramenter marker" in it is simply treated as a regular character. That's why you saw the "|1" still in the statement in the snapshot.
At this moment, a way I can think of to work around this problem is to create a statement for each of the actual values, and give this bunch of statements to WorkloadDriver ...
We're working on modifying the code to solve this problem!
Thank you for using TPoX!
Kevin Xie
Hi Hidehiko,
Please download the following files from the project's SVN:
DatabaseOperations.class (place in the TPoX/DB2/classes folder)
http://tpox.svn.sourceforge.net/viewvc/\*checkout*/tpox/TPoX/DB2/classes/DatabaseOperations.class?revision=262
WorkloadProcessor.class (place in the TPoX/WorkloadDriver/classes folder)
http://tpox.svn.sourceforge.net/viewvc/\*checkout*/tpox/TPoX/WorkloadDriver/classes/WorkloadProcessor.class?revision=262
Transaction.class (place in the TPoX/WorkloadDriver/classes folder)
http://tpox.svn.sourceforge.net/viewvc/\*checkout*/tpox/TPoX/WorkloadDriver/classes/Transaction.class?revision=262
The new code supports "|1"-style parameter markers in NSE search queries.
Let us know how it goes. Thanks!
Mike