All
Accepted answers
Brendan Bannon · May 4, 2017 go to post

Just to make it as simple as possible as I am guessing you are new to Caché

ClassMethod Export(pGlobal, pFile)
{
    set file=##class(%File).%New(pFile)
    do file.Open("WN")
    set key=$order(^Global1(""))
    while key'="" {
        do file.WriteLine(^Global1key))
        set key=$order(^Global1(key))
    }
    do file.%Save()
}

$ORDER() is a command that returns the next defined subscript value.  When you pass in "" it returns the first defined subscript value, 1 in your case.   After writing the value of ^Global1(n) to the file the final $ORDER() will return "" indicating there are no more defined subscript values.

for more details see our docs for $ORDER()

Brendan Bannon · May 2, 2017 go to post

John

You did not overlook anything, we at ISC did.  The Enterprise Manager was never formally released or pulled shortly after release.  I have removed the reference to it from the WRC Distribution page.

I will ask Product Management to comment here about a future plans in this area.

Brendan Bannon · Apr 20, 2017 go to post

Susobhan

The example you have above should have no problem handling the number of rows assuming the Business Logic is not building some huge thing in memory.

%Library.ResultSet has been replaced by %SQL.Statement.  We would expect %SQL.Statement to give you better performance then %Library.ResultSet.

To increase performance a little more Embedded SQL gives the best performance of all three options.  In older version of Cache the difference was pretty large.  In current version of Cache the difference is not that great.

Brendan

Brendan Bannon · Mar 23, 2017 go to post

Hello

Are you storing 64365 or 64365,23587?

If you are storing the first then the above answers are correct.  If you are storing the second then you will need to create your own Data Type class based on %Timestamp and write your own LogicalToStorage and StorageToLogical methods.

Cache has system functions that will convert $H to a readable format and back

USER>W $zdatetime($h)
03/23/2017 06:36:38
USER>W $ZDATETIMEH("03/23/2017 06:36:38")
64365,23798

Brendan

Brendan Bannon · Mar 22, 2017 go to post

Susobhan

Yes you can use any DML statements over ODBC to create new users and GRANT and REVOKE privs.

Create User:  http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY…

Grant :  http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY…

Revoke:  http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY…

We do not recommend making general use of the _SYSTEM username.  For a secure system you should either disable this account, or change the password and limit it's use.  Then create a new Cache User for each person connecting to the application.  You can first create a Role that has all the privs needed for a general user and then just GRANT that one Role to each User.

If you need more detailed examples on this you can contact InterSysetms Support and we will help you out.

Brendan

Brendan Bannon · Mar 13, 2017 go to post

There have been some conflicting info in the answers to this question so I am going to try and put all the correct info into one place.

Pluses for Process Private Globals:

  1) no size limit

  2) cleaned up by the system when the process exits

  3) explicit KILL will clean up PPG

  4) can be used in a class with Cache SQL Storage

Pluses for % Array:

  1) faster than PPG

  2)  cleaned up by the system when the process exits

  3) explicit KILL will clean up % array

  4) argumentless KILL will clean up % array

  5) Can be used in the NEW command

  6) can be used in a class with Cache SQL Storage

Negatives for Process Private Globals

  1) slower than % Arrays

Negatives for % Arrays:

  1) limited in size by the process memory

I hope this list is close to complete and accurate.

Brendan

Brendan Bannon · Mar 6, 2017 go to post

Derrek

From a COS coding point of view you can get yesterday by setting Yesterday = $H-1

To get the number of days this month you can do the following:

s YearMonth=$P($ZDATE($H,3),"-",1,2)
w !,YearMonth
s StartDate=YearMonth_"-01"
w !,StartDate
s DaysThisMonth=$H-$ZDATEH(StartDate,3)
w !,DaysThisMonth

of course all that can be squished into one line:  

w $H-$ZDATEH(($P($ZDATE($H,3),"-",1,2)_"-01"),3)

Looking at the DeepSee docs it looks like you can get some of the stuff using Now

http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY…

year = birthd.[year].NOW

month=birthd.[month].NOW

I am not an MDX guy so if this is the direction you want to go in you should talk to one of our DeepSee support people and I am sure they can tell you how to do this.

Brendan Bannon · Feb 27, 2017 go to post

The default behavior for %String is case insensitive searching, so I would expect what Bachhar shows would be the same if he left off Collation = 'SQLUPPER".

It is possible to change the default collation for %String on a system, so maybe that is why you are seeing different results.  For example this command would make the default behavior %EXACT:


USER>w $$SetEnvironment^%apiOBJ("collation","%Library.String","EXACT")
1
USER>w $$GetEnvironment^%apiOBJ("collation","%Library.String",.current)
1
USER>w current
EXACT
USER>

This is done on the namespace level

brendan

Brendan Bannon · Feb 21, 2017 go to post

the format of an Object Id is $LISTBUILD(id,class) so when I delete a row from Sample.Person I get:

SAMPLES>zw oid
oid=$lb("4","Sample.Person")

The Id is the first $List element:

SAMPLES>w $LISTGET(oid,1)
4
Brendan Bannon · Jan 9, 2017 go to post

The ORDER BY needs to go with the TOP, so your query should be

SELECT Name,%VID,ID,Age FROM
   (SELECT TOP 10 * FROM Sample.Person ORDER BY Name) 

then it does what you want.

Brendan Bannon · Jan 9, 2017 go to post

You could do the same thing by creating a View with a function in the WHERE clause that called the method to figure out if you should see the row or not.

I think this is a cleaner solution as you do not need to worry about the %RLI becoming available again.

Brendan Bannon · Dec 8, 2016 go to post

Scott

This is a bug that ISC Development needs to look at.  I spoke to the developer about it, he understands what is going wrong and will look at options to fix this.

The problem is the format of the data in the index does not match the format of %TimeStamp.  This Property is defined as a %TimeStamp but we are only storing the date part in the index.   If you modify you query to something like this then it should return the correct answer:

SELECT MAX(IndexedDate)
FROM HS_IHE_ATNA_Repository.Aggregation
WHERE DATEPART(sqltimestamp,IndexedDate) < '2016-11-28'

If you would like to track the Prodlog you can open an WRC issue and tell the advisor to come and talk to me about this.

Brendan Bannon · Dec 6, 2016 go to post

Mark

 

Running TuneTable is safe to do at any time.  It will be CPU intensive so you might not want to run it at peak workload times, other than that it is fine to run at any time.

 

It is important that all your tables have this info so it is great that you are running it on all your tables.

Brendan Bannon · Dec 2, 2016 go to post

The first question for any SQL performance issue is:  Have you run TuneTable?

It is very important to have correct values for ExtentSize, Selectivity and Block Count, whether the info comes from a developer manually entering it or by running TuneTable.  With out these values the Query Optimizer can only guess what the right plan might be.

The next step is to provide proper indices to support the queries you are going to write.  Without have a lot more info it is impossible to tell you what those might be or what type of index would be best (bitmap or standard).

Brendan Bannon · Nov 30, 2016 go to post

I like Tim's method better.  While I understand the desire to hide complexity in the SQL you are also hiding information about the work that is needed to resolve the query.  What if it would be faster if the query first looked at the AppName.BO.DatabaseFunctions table and then looked at the ApplicationName.DB.MedicalCoding.ICDAutoCodeDefn table?  When you are hiding the join in the compute code that is not an option.  Using the -> syntax keeps the query pretty simple but still leaves all options open to the Query Optimizer.

Brendan Bannon · Nov 30, 2016 go to post

can you please start a new question for this error.  Include the SQL and the full error message.  -400 should be reported for an COS error like <UNDEFINED>,  <SUBSCRIPT>, <FILEFULL>....

Brendan Bannon · Nov 29, 2016 go to post

Jiri

You have a couple of options:  I created an index in Sample.Person that is the same as yours and then look at this query

SELECT Name,FavoriteColors FROM Sample.Person
    WHERE FOR SOME %ELEMENT(FavoriteColors) (%Value = 'Red')

It uses the index and should give very good performance.

  • Read index map Sample.Person.FavColors, using the given %SQLUPPER(Subvalue(FavoriteColors)), and looping on ID.


  • For each row:
  •  Read master map Sample.Person.IDKEY, using the given idkey value.
     Output the row.

Have a look at the docs for info on Indexing Collections

The above is a little different as it is a List instead of an Array, but it should still work.

If you want better performance out of the Child Table created for the array you need to define the index to be on both the Element and the Key.  I think if you look in the Management Portal at the maps /  indices for your table you will see the one you defined is not projected in the child table.

I added the following Property and index:

Property Kids As array Of %String;
Index KidIndex On (Kids(ELEMENTS), Kids(KEYS));

and for this query:

SELECT id FROM Sample.Person_Kids WHERE Kids = 'Kieran'

I get a plan that uses the index.

  • Read index map Sample.Person_Kids.KidIndex, using the given %SQLUPPER(Kids), and looping on element_key and Person.


  • For each row:
  •  Output the row.

Either way should give you good performance.

hope this helps.

Brendan

Brendan Bannon · Nov 29, 2016 go to post

In 2012.2 and up you can use SQL Runtime Stats to gather the above info plus Global Refs and Lines of Code for every query run.  This has more overhead and take up more disk space so please remember to turn this off after you gather the needed info.

If you have questions after reviewing the docs I would be happy to answer them:  Brendan@interSystems.com

Brendan Bannon · Nov 22, 2016 go to post

lots of options!

If you are going to change namespaces make sure you move to the new namespace, create and use your Object and then move back.  Cache does not support using an object opened in one namespace in a different namespace.

How are you doing this now in COS?  If you are using extended global syntax you could still do that in the class definition.   If you are using Global Mapping in the Namespace you could include Package Mapping to expose the classes as well.

If you have data in 2 databases that you want to expose in one class you can create a class using Cache SQL Storage and "map" the two globals into one class def.  I can provide an example of this if needed.

As for add SQL to your application, using Embedded SQL with cursors is the fastest way to go.

If you want to use a resultset, ISC encourages you to use %SQL.Statement.  The sample code above is a great example for %SQL.Statement, it is a dynamic query that is customized based on user import. 

If the query never changes use Embedded SQL.  If you feel the need to stick with a resultset then put the SQL in a class query and execute that.  Why waste the time preparing an SQL statement over and over again if it never changes?

Brendan Bannon · Nov 14, 2016 go to post

I agree that Journaling off is a likely cause.  I just tested you code on 2015.1 and i did not have this problem.

Brendan Bannon · Nov 14, 2016 go to post

Matt is correct Bock Count has been part of the SQL  Optimizer from the very beginning when we had 2K database blocks.  

In the past this number was an estimate.  Now it is calculated as part of TuneTable.  We did not want to change all our internal structures so we just count the number of 8K blocks on disk and divide by 4.

Brendan Bannon · Nov 8, 2016 go to post

While for the most part this new feature has improved SQL performance that is not always the case.  Introducing the Outlier value can greatly change the Selectivity of a property as you can see in Matt's example:  Status SELECTIVITY went from .25 to .0333.  This change in selectivity can make this index look better to the optimizer and cause queries to use different plans.  Please test your applications carefully when upgrading from before 2013.1 to a version higher than 2013.1

Brendan Bannon · Nov 7, 2016 go to post

Ariel

 

Attached is an example of what I think is the correct way to do what you want.  I have created 2 classes to map the 2 different globals and then created a third class that is a view that exposes the info from the 2 classes in the way that you want.

customerexample.zip

 

Brendan

Brendan Bannon · Nov 6, 2016 go to post

You can only have 1 data map in a class.

 

I strongly recommend you create 2 classes and then write a view to join them together,

 

Brendan

Brendan Bannon · Nov 3, 2016 go to post

So I don't think you class would even compile.  The Data map needs to have all the stored fields in it and it looks like you have something called {sub2} that is not in the Data map, it is just in the index.  If it is an SQL Computed field then the class might compile.

 

Based on your comments it sounds like you are pretending that the ^SPMRMA global is an index when it is really the data map of a second class.

 

I don't think this will save you much if any in performance over writing the JOIN.

 

If you really want to do this I would say that ^SPMRMA should be the data map and then to get VendorName out of ^TBL you can write Retrial Code in the data section or you might be better off using compute code in the property def.  In Data Retrieval code you can only use {L#} so you would need to write some code to get a {VendorNum}  In compute code you would be able to reference {VentorNum}.  Going in this directions you would not have an index map, just the Data Map.

 

While the above will work, I still don't think it is a great idea.

Brendan Bannon · Oct 19, 2016 go to post

Does the User you are logging into the portal with have %All?

You could turn on Auditing and enable Privilege violation auditing and see if something shows up there.

did you run $SYSTEM.OBJ.Upgrade() on the namespace?