With JMP 17, In the query builder, how can add "WITH UR" at the end of the SQL code?

[#1]

Hi,

     I have to execute sql queries against IBM DB2 Servers. My DBA (Database Administrator) told me to add "WITH UR" at the end of every single query that I run. It's supposed to reduce the chances that my query creates a table lock with impact on the applications that feed the tables that I'm using. I don't know if it's possible to add "with ur" at the end of the sql query generated by the JMP Query Builder. I could convert to SQL the result of the query builder, then manually add the "with ur", but then I loose the possibility to modify my query with the builder. 

For example, in the query below, the last line of code is "with ur". Without the "with ur" this query might be armfull to our mfg environment, because it could create a temporary table lock. If I want to get the benefits of using the Query Builder, I need to have the "with ur" added to the SQL.

SELECT
   *
FROM
   ABC.SOME_TABLE AS T1
WHERE
   DATE(START_TMST) = CURRENT DATE - 2 DAYS
WITH UR

I'm new with JMP, sorry if my question is dumb simple...

Thanks for your help.

[#2 · Accepted solution]

You can convert the SQL in QueryBuilder to custom SQL as mentioned here:
https://www.jmp.com/support/help/en/17.1/index.shtml#page/jmp/write-sql-statements-in-query-builder....

Keep in mind this is a one-way process. That is, JMP doesn't re-parse the SQL. It sends it to the Data Source (db2 in your case) as written.

[#3]

Thank you very much for the response. I know it's possible, but I'm hesitant to implement it in my environment. Technically, this is a very good solution, but difficult to implement among the many JMP users I support. They will need to convert to custom SQL in the early stages of query construction to partially test their query. At this early stage, MANY bugs appear (ex: missing key which involves a cross join with millions of records selected in error). This is the phase where they most need protection “with ur”. And to get it, they would have to use a one-way process and forgo using the Builder.

I'm in discussion with the DBA to see if we could do something about the DB2 client app. on the PC. It might be possible to add an "uncommitted read" command in the connection tool config. This would remove the need for the "with ur" in the SQL statement. This would allow safe use of the excellent JMP query generator.

I'll mark my question as "accepted", because it's technically valid and it tells me that there aren't more options in JMP to reach my goal.

If we succeed to solve the problem by a config to the db2 client, would you be interested to know it?

     Thankyou very much, Best regards

[#4]

Perhaps it's worth to add some advanced SQL options to the Query Builder?