New post
Question
Kevin Furze · Jan 15, 2020

sql statements - how to clear multiple sql statements and Frozen Flags

#Code Snippet #SQL #Caché

cache 2017.2.1 (Build 801_3)

when I look into the "sql statements" ( sql page of the management portal ) I have lots of old statements with a lot of them frozen. many of them are %sqlcq routines (I believe these are the SLQ's I run when testing/running random SQL queries in the sql page. )

I can clearly see where I can click on an individual query and untick an individual query to unfreeze it. or delete it. but thats a painful slow process. I can't see anywhere to delete/change multiple queries

I want to programmatically remove all the %sqlcq queries from the list and then start looking at all the remaining queries,

has anyone got any code that allows me to

  • delete certain queries from that list
  • remove "frozen" from the queries
  • ultimately clear the entire list and let the list grow again with all the new 'unfrozen' queries

all of this is NOT being done on LIVE data, it's a copy for experimenting on the SQL.

I can then run stats on the LIVE data and then the copy data comparing the two

0
0 175
Discussion (2)0
Log in or sign up to continue
Kevin Chan · Jan 15, 2020

To unfreeze:

  • Do $SYSTEM.SQL.FreezePlans(0,1,,.Errors) // Unfreezes all SQL statement plans in the current namespace
  • Do $SYSTEM.SQL.FreezePlans(2,1,,.Errors) // Unfreezes all SQL statement plans in the current namespace that were marked Frozen/Upgrade.

Then execute $system.SQL.Purge()

That should unfreeze everything and delete all CQs.

0 0
Kevin Furze · Jan 15, 2020

that was fast, just 13 minutes for an answer,  nice one

I'll test it tomorrow, but in the mean time, looks like I'll accept this as the answer

0 0