Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I know that dsqgen is used to transform the query templates into executable SQL. But when I type in the command line: "dsqgen /directory query_templates /template query1.tpl /dialect sqlserver /scale 5", there's an error that "Substitution 'STATE' is used before being initialized". I also copied the ansi.tpl and tpcds.idx files in the same directory as dsqgen.exe because dsqgen always read these two files. Anyone has any idea how can I solve this? Thanks in advance.

share|improve this question

I got the same error, the first thing I tried was to erase query1.tpl from the templates.list, but then I got a similar error for query2.tpl but referring to the substitution '_END'.

I looked at the template and realized that the variable was never used, then I looked in the QGEN.doc guide and read that the variables are automatically set for use of benchmark sponsors. So after struggling with it, I decided to make a simple solution: I changed every template (with a script) and added the line:

define _END = "";

At the beginning of each of them, and voilà, problem solved! However, I was still getting similar errors for some queries (like query1, where the variable STATE is referred but not used), so I just took those queries out of the list and I was finally able to generate 68 out of 99 of the queries. I guess that for tpcds toolkit v1.1.0 apparently there are errors in the query templates.

Hope it works for you, good luck!

share|improve this answer

You can download fix error "STATE" and endcoding dump data to database at

share|improve this answer

Hint: instead of adding define _END = ""; to each template, it can be added into dialect (netezza.tpl for example). This has fixed compilation of all the templates on the latest tpc-ds 1.3.1.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.