HCE-5920 practice question 14 of 15

You are developing the PDI solution for a customer. The customer requires that a set of SQL statements are executed on each connection to the database. Which method will satisfy this requirement?

Full set: 60 questions
Answer options
A: Use JNDI to specify the SQL statements.
B: Use the Advanced section in the Database Connection window to enter the SQL statements. Correct answer
C: Use the Options section in the Database Connection windows and pass the SQL statements to the database as a parameter.
D: Use the Database Connection wizard and specify SQL statements.
Correct Answer:
B. Use the Advanced section in the Database Connection window to enter the SQL statements.
Explanation

In Pentaho Data Integration (PDI), the Database Connection window is used to define how to connect to a data source. This window has an "Advanced" tab that contains a specific text area labeled "SQL to execute on connect". Any SQL statements entered into this field are automatically executed by PDI every time a new connection to the database is established using this specific connection definition. This feature is designed precisely to handle requirements such as setting session-specific variables, temporary tables, or security contexts immediately upon connection, ensuring a consistent environment for all subsequent steps in a transformation or job.

Why Incorrect

A. Use JNDI to specify the SQL statements. JNDI is a naming service for looking up centrally managed resources, like database connections. The on-connect SQL is configured within the PDI connection definition, not as part of the JNDI name lookup itself.
C. Use the Options section in the Database Connection windows and pass the SQL statements to the database as a parameter. The "Options" tab is for setting driver-specific parameters (key-value pairs). While some JDBC drivers might support an initialization SQL parameter, this is not the standard, database-agnostic PDI method.
D. Use the Database Connection wizard and specify SQL statements. This answer is too general. The "Database Connection wizard" is the entire interface for creating the connection; the "Advanced" section is the specific location within that wizard where this setting is configured.

References

1. Hitachi Vantara - Pentaho Documentation

"Database Connection": The official documentation details the user interface for setting up database connections. It explicitly describes the "Advanced" tab and its functionality.

Reference: In the "Database Connection" dialog documentation

under the "Advanced" tab section

the field "SQL to execute on connect" is described as the designated area for SQL statements that run each time a connection is made. (This can be found in any recent version of the Pentaho Data Integration documentation on the Hitachi Vantara knowledge base

typically under a path like Pentaho Data Integration > Working with PDI > Defining Database Connections).

Scroll to Top