IDMS

CA IDMS Tech Tip by Edward Gorga, Principal Support Engineer for July 21, 2015

Many network databases contain date fields that are stored in two-byte numeric fields – i.e., PIC 9(2). When referencing these fields in SQL statements, when the value is less than 10 then any  leading zero is suppressed and the remaining digit is left-justified. These fields cannot be used in SQL date comparisons and date arithmetic. 

To use these fields in SQL date arithmetic the network data must be translated into a character field in a format SQL will recognize as a valid date.

For detailed instructions on doing this see Technical Document TEC1857929

There is a ZIP file here in the IUA/EIUA Community which is called "IDD Reporting Using IDMS/SQL". It is relevant to this post as it contains a couple of Cobol programs - one of which implements a "Gregorian Date to SQL Date Function". It would be an excellent example to use to implement an IDMS/SQL function to convert your shop's non-SQL internal database date format to something that is SQL compliant. The PPT that is in the ZIP shows numerous examples of how the IDD Dictionary Gregorian dates can be used in SQL Date Arithmetic  - you could use similar syntax for your application databases (for the IDD reporting SQL Views are provided to help out). An example follows:

--

-- Tell me all the records changed in the last 90 days by Users starting with "J" ! 

--

select name, version, revisedby, reviseddate                          

  from RSQLSHR.RECORDCHANGED                                           

  where ymd_sincemod < 90 and revisedby like 'J%' ;

HTH - cheers - GaryC

https://communities.ca.com/docs/DOC-15853715  or  IDD Reporting Using IDMS/SQL.zip --> either link should get you to the document mentioned above!

The text in this technical tip delivers a very small function written in SQL procedural language that can assist with date arithmetic. I have not done performance comparisons, but I believe that this approach may be more efficient than using COBOL programs that are invoked from a procedure, because they remove the call to the COBOL program...  but both are valid approaches.

Scalar function DIGITS actually performs same task as what is shown in example, but I understand it is just an example.  There are some problems in the SQL sample provided.  Using the 2 character function against the four character Start_year won't work and there is a stray ) in the line that applies the function to the Start_day field. 

Not having the SQL option I was able to create the function but I'm getting a PINT error at runtime.  I have an issue open on it and hopefully that part will get sorted out.

Thanks for catching the errors in the technical note. I've corrected the document and submitted it for re-publication; the new version should be available shortly.

Also, you are correct in saying that the DIGITS scalar function will provide the same result as the function defined in the technical note. If folks want to use that instead, DIGITS can be substituted for the user-written function in all of the subsequent steps outlined in the technical mote.