IDMS

Found a discussion here on formatting and OUTFILE   but I couldn't quite see how to use that info to answer my question...

https://communities.ca.com/message/60728418#60728418

I wrote a query in OLQ, saved the qfile.  I decided that what I want it just to save the COUNT(*) of the number of rows to a file so I changed the SELECT to SELECT COUNT(*).

If I change SYSLIST to  a file, even with headers turned out I am getting other information I do not want but I do get the COUNT displayed as a DECIMAL NUMBER.   

I tried to use OUTPUT OUTFILE in order to JUST get the value in a file and That is working except. . 

The problem is the COUNT is in HEX and I want a decimal.   Now in Oracle I can use a TO_NUM or TO_CHAR function to manipulate the field.

  But I can't seem to see how to format the COUNT to a decimal,  I even tried adding an EDIT for SQLCOL00001  which seemed to be the alias for the column.

(I also cannot do SELECT 'RESULT=', count(*) from. . ,  in OLQBatch).

Maybe this is simple but for some reason I am not seeing it. 

Any Ideas?

Thanks!

Cindy Kline

You know - I'm thinking - if we could see the code that gave you the DECIMAL output -  and the code that is giving you the HEX output - it might be easier to offer some suggestions. Cheers - GaryC

sorry   It seems like I have two parts - how to put just the results of a query into a SYSOUT or OUTPUT file.

And how to format a numerical result, like COUNT(*) to Decimal rather than Hex.

This code  gives a hex value in bytes 1 through 3 on the output file   (I also tried doing NOECHO to see what shows up in the SYSLST)

SET USER '******                         '                        

SET ACCESS OLQ                                                      

SET DICTNAME ' '                                                    

SET UNDERLINE '-'                                                   

SIGNON SS subschemaNm SCHEMA schemaNm (   1)                            

OPTIONS ALL NOHEADER ECHO NOFILLER FULL PARTIAL INTERRUPT OLQHEADER -

NOPATHSTAT NOSTAT NOCOMMENT VERBOSE NODBKEY PICTURE CODETAB NOSYN   

SELECT COUNT(*)   FROM 'REC1', 'REC2' -                          

      WHERE ('REC1-REC2')                                    -               

           AND (DATE-RCVD BETWEEN 130701 AND 140630)   OUTPUT OUTFILE 

  BYE                  

With hex 0s to the end of the file   (00453 hex  = 1107 decimal )

     00500000000000000000

     04300000000000000000

When I don't use the OUTFILE the only difference is the removal of the DD for the OUTFILE and the removal of "OUTPUT OUTFILE" plus adding DISPLAY back in.

     SELECT COUNT(*) FROM 'REC1', 'REC2' -

    WHERE ('REC1-REC2') -

       AND (DATE-RCVD BETWEEN 130701 AND 140630)

  DISPLAY

  BYE

I tried to turn off the header, qnd echo, etc, because I just want the data field(s)  in the output  and without the OUTFILE and putting the DISPLAY in, I get execution information as well as the COUNT(*) value of 1107

OLQ 107019 00 Copyright (C) 2010 CA. All rights reserved.

SET USER 'xxxxxxx '

sET ACCESS OLQ

OLQ 092033 00 Processing mode changed to OLQ.

SET DICTNAME ' '

OLQ 092018 00 The default DICTNAME value has been modified.

SET UNDERLINE '-'

OLQ 092027 00 Underline character has been modified to -

SIGNON SS subschemaNm SCHEMA schemaNm( 1)

OLQ 100021 00 Ready to retrieve data from subschema schemaNm

OLQ 100022 00 Schema: schemaNm Version: 1

OPTIONS ALL NOHEADER NOECHO NOFILLER FULL PARTIAL NOINTERRUPT OLQHEADER

NOPATHSTAT NOSTAT NOCOMMENT VERBOSE NODBKEY PICTURE CODETAB NOSYN

               1107                     <<<< this is the value

END OF REPORT

I CAN write programs to execute the multiple queries and format a file. But these queries are not going to be run very frequently and they are easy to produce with OLQ. I just don't want to have a lot of manual intervention.  The users of this information are outside of IT, and are probably going to import this data, along with data from other sources, into spreadsheets. 

Thanks!

Cindy

Sorry it took a while to get back to you ... but with time to think about this ... I seem to recall that as soon as you direct the output to a "file" DD rather than the "report output" DD - that the assumption is there will be post-processing by another program - so you get "machineable" output rather than something for a human!

What your display showed us is that you are NOT getting HEX output, what you ARE getting is binary (i.e Cobol COMP) output which is suitable for a program to process. I have not had a chance to get to a manual - but if I were a betting man I'd bet that you cannot override this. I'll try to verify this later ... if I get a chance ...

HTH - cheers - GaryC

According to the manual - in the output listing you should get a display of what the OUTFILE will look like - did you look for this in your output - it would be interesting to see the Size, Decimals, and Data Type in particular! Did you get such a listing when you ran your report to get the COUNT? If the users are just going to import the COUNT, and whatever, into something else - then they just need to understand the layout and format of the file - which should be documented by OLQ as shown below!

Just curious - cheers - Gary

Hi Gary,

(I guess I did not post my earlier response yesterday - oops)

Yes it was listed as binary.    (but it looked like hex to me in the display and I just wrote hex.)

I had assumed that OLQ would "consider" the EDIT I added in an attempt to deal with this but I guess that is just for display?  

I did see this

OLQ 149018 00  File name Field name  Offset   Size Dec no  Data type                                                                         

               OUTFILE   SQLCOL00001       0000   0004   0000   BINARY

EDIT SQLCOL00001   ALIGN RIGHT   OLQHEADER ' '  PICTURE   '-ZZZZZZZZZ9'

PAGE HEADER BLANK LINES AFTER 1  LINE 1 'REC1/REC2 REPORT' CENTER LINE 2  

  '$DATE'  CENTER                                                             

PAGE FOOTER BLANK LINES BEFORE 1   LINE 1 '- $PAGE -'  CENTER                

BYE                                                                          

My work is for a user department ,not directly for IT and I am trying to make this as easy for these non-IT folks as possible.  I will have data going to them from other sources outside of IT and they likely will want to import the data into a spreadsheet.  It would have been nice to just have a bunch of Batch OLQs run and get us the data to FTP over to them and then import it.  Now I guess I am looking at having to either do all the queries and then have something reformat the data, or writing these queries in another language that will allow us to format the data. (?)    As I typed this I guess I could just use something like a sort to reformat, but still not thrilled with needing another step.

Have you considered using SQL SELECT with BCF? You could generate "comma separated variables" and import directly into XLS, other SQL databases (gasp), etc! Your existing SQL for OLQ would be pretty close to what you want to use for BCF ... so you're better than 1/2 way there ... and you have CAST ...  AS  .... and all sorts of other SQL functions for formatting purposes. Thank you for showing the OUTPUT file definition - confirms my suspicions about the OUTFILE - OLQ decides what it will look like - but at least it tells you what you're getting!

HTH - cheers - Garyc

Gary,

Slow response here... this site does not allow us to use BCF.  I had asked about using it for retrieval only but it is just not available.

The DBA did tell me that I could use Culprit.  I used to use Culprit a lot but honestly had no interest in using it for something like this.  But apparently you can execute a SQL query in Culprit similarly to what you can do in OLQ Batch(?)

(What the heck is with this editor? creating this weird spacing and boxes when you try to paste?)

       IN DB(Q) DICTIONARY=CORPDB  SCHEMA=SQL_SCHEM

       SQL SELECT COUNT(*) AS COUNTW

Hi Cindy,

Not sure if you have worked around it but another approach would be in the version where the number come out readable, direct the syslst to a file, and then parse the output with a REXX routine.  That's not very hard to do.  Then you could even FTP or E-mail the value if you have means to do that in your environment or just display it in the job.

GL,  John

You can do:

   SET CONTINUATION CHARACTER \                 

   SELECT CONCAT(NEXIH(COUNT(*))) FROM table \ 

   OUTPUT ddname;                             

I then get the count neatly right justified with leading spaces and trailing x'00', but if you make the record length the length of the count (16 bytes) it will be fine.
I am not sure why I need NEXIH, but, that is what my old query had, without it I get a S0C4.

You write "I also cannot do SELECT 'RESULT=', count(*) from. . ,  in OLQBatch"
Do you get a S0C4 abend?

R18.5

Test 1  base line

SELECT 'USER-047'.'USER-NAME-047' FROM 'USER-047'

OLQ 098006 00  712 whole lines and 0 partial lines in report.

OLQ 098007 00  712 records read.   712 records selected.

Test 2 No output

SELECT 'RESULT=', COUNT(*) FROM 'USER-047'

OLQ 143037 08  This column list is not valid for GROUP BY/aggregate functions.

OLQ 090017 08  Unable to process Select request due to record build error

OLQ 100029 00  Signoff accepted - OLQ session terminated.

Test 3 S0C4

SELECT CONCAT(NEXIH(COUNT(*))) FROM 'USER-047'

IEA995I SYMPTOM DUMP OUTPUT  743

SYSTEM COMPLETION CODE=0C4  REASON CODE=00000011

  TIME=10.57.36  SEQ=21207  CPU=0000  ASID=0039

  PSW AT TIME OF ERROR  078D1000   A22586D0  ILC 4  INTC 11

    ACTIVE LOAD MODULE           ADDRESS=222556C0  OFFSET=000030

    NAME=OLQSSCAN

    DATA AT PSW  222586CA - 10004BF0  80E29680  F0084AF0

The 0C4 in OLQSSCAN may be fixed by:

Check to see if you have that on.

True RO74586 is documented to fix a S0C4 in OLQSSCAN , problem is it has a service pack as a pre-req RO71145.

Sadly, If I have a critical production issue. I cannot simply put on a single PTF to resolve.  I must but on a service pack.

I don’t like this idea.

++PTF (RO71145)

/*****************************************************************************

MAINTENANCE TO SYNCHRONIZE Z/OS AND Z/VSE 18.5

PROBLEM DESCRIPTION: Except for some special modules, CA IDMS maintains a

common code base across all operating systems. This update reflects

maintenance and changes made in source modules for the 18.5 level across

z/OS and z/VSE. New and updated features are described in the CA IDMS

Release Notes 18.5.00 2nd Edition.

CARS1408 has RO71145 and CARS1411 has RO74586 so you would need the maintenance up to CARS1411.
Use this site CARS.

Rob,

The old superzap method of maintenance had it's downsides too, but it was handy when you needed to apply just one fix to a module or even back off just one fix.  I have a similar quandary now with recently released RO80338.  We don't have a zIIP processor, so if I understand correctly, we are not subject to the 0F8 that can happen which the fix addresses, but it touches so many critical system modules that it likely is soon to be a prereq. for something I do need to apply.  I'm also at a point where 18.5 is partially rolled out to production so that provides maybe some options but also some complications.   RO71145 I manged to get worked in before I started converting CVs and pretty much had to as it was a prereq. for a fix on an open issue I had.  RO71145 a prereq. for a large majority of what has followed so at some point I would think everyone on 18.5 probably needs to get it on to be positioned to be able to apply new maintenance.

John

Hi John

In the event a need arises for a PTF, one must be “bleeding edge” production current.

If not, your positioned to be forced to rollout 10s, 100s and even 1000s of modules just for a single fix.

This model sure isn’t designed to simplify/reduce the daily/annual costs of IDMS, in does just the reverse.

Anyhow , Thank You for taking the time to reply.

Rob

Hi Cindy -    Sorry it's taken me a while to get to you on this thread. Have you tried using the CAST function? This is embedded in the query itself, and transforms the format of an output column into another format. It is docuemnted in the SQL Reference manual. An example might look like this:

SELECT CAST(COUNT(*) AS INTEGER)  FROM 'REC1', 'REC2' -                        

      WHERE ('REC1-REC2')                                    -            

          AND (DATE-RCVD BETWEEN 130701 AND 140630)  OUTPUT OUTFILE

If you try the CAST and it doesn't work, please let me know. Also, I would be happy to open an issue in our support site and work with you to resolve this. Regards, Cal

Please ignore my previous update on this thread... the CAST function I suggested is only supported when ACCESS MODE IS IDMS; the OUTFILE is only supported when the ACCESS MODE IS OLQ.  Sorry about the misleading direction; I'll reflect on other options...

Cynthia, have any of the ideas above solved your issue?