Understanding CONstant usages in 2e.

One of my colleagues was trying to track down some (potentially) hardcoded string values/literals.  She had already used the SQL queries in one of my previous posts to narrow down the usages inside messages, however, she was also concerned that there might be some underlying hardcoding via CND and more specifically via CON values.

As a side, IMHO there should be next to no CON usages in a well architected model apart from, values like *BLANK, 0.00 and some tolerance towards values like 12 (months), 52 (weeks), 365 (days), 100 (percentage).  I'd even accept 1,2,3, 9999999 etc.

I draw the line at meaningful literals that could have been scoped conditions (CND) or database values.

My main reason for this is that there isn't a way (via the 2E tool interface) to check for usages of CON values.  Obviously, we can scan the generated source, but then what about all the genuine field names or comments.

Back with 2E!  When generating the source, it somehow lets the generator knows about them and adds them into the code (inline) or as part of a data structure.  Take a look at the bottom of a source listing if you don't believe me.

Here is a sample function using some constants.  Note the 'Create Flatfile' call is passing in 'FLAT FILE CoNsTANT' as CON.

And here are the relevant code snippets of generated source i.e. The moves.

Followed by the structure of CON values at the bottom.  Note: Sometimes the CON is generated 'inline' at the point of the MOVE.

Anyhow, in order for the CON values to be consistently applied, they must be in the 2E model data files somewhere, with the theory being we should also be able to query them.  As CON can only be applied via action diagram text, let's start by looking there in the YMSGACTRFP file.

It looks like we have some constants referred to in field ELMTTL ** (Element Title - highlighted RED).  This could be a good start but there are some obvious limitations. Whilst it looks like it covers basic *MOVE and *CONCAT field assignments, when we have hardcoded values going into a function call (PURPLE), the CON value isn't reflected in the ELMTTL field.  

**It would appear that the ELMTTL data is used for the AD layout.  Perhaps I will expand more on the complexities of this data in a future post!!!

However, it looks like 2E maintains a surrogate reference for each unique CON value used in the AD regardless of AD syntax used.  (GREEN)

This is awesome, but how are these linked via the function AD?  

To do this, let's take a gander at a subset of the 2E files in the model library. The one highlighted below looks like it could be useful and most likely, a file most dev's didn't even realise existed.  Hands up who has explored the intricacies of the model schema.

What does the data look like in the table?

Bingo, we have our constants.  

Luckily ours are at the bottom of the file :-) for easier blog documentation.  Diving deeper, we have now confirmed that 2E maintains a surrogate reference for each unique CON value and if we refer back to the YMSGACTRFP image above, these are all highlighted in the GREEN section.

So, all that is left for us to do is to identify the CON values we want to analyse, work out what functions they are linked to via the AD code and some pretty cool impact analysis is before us.

Here is a sample SQL retrieving the many different usages of the term 'constant'.  As CON is only ever associated with field @@SUB3, the query is quite straight forward.

Ensuring you have the correct library list.

SELECT DISTINCT b.@@msg, c.OBJNME, b.@@SUB3, a.CON, c.OBJOWN    

FROM YCONDTARFP a LEFT JOIN YMSGACTRFP b ON a.@@CON = 

b.@@SUB3 LEFT JOIN YMDLOBJRFP c on b.@@MSG = c.@@OBJ  

WHERE UPPER(a.CON) LIKE '%CONSTANT%' AND c.CUROBJ = 'Y'                       

This query returns unique records for each of the functions using CON context with the word 'constant', the UPPER() function ensures we don't miss any based-on case differences.  The rest of the query does a basic join to return some additional fields from YMDLOBJRFP (This is the *ALLOBJ or *A model list).  You can add to this query or tidy up names and formatting however you like.

I hope that this helps someone.

Thanks for reading.
Lee.

Exploring 2E messages via SQL (Update)

Last year I made a small post about querying the underlying 2E files for messages, this use case came up at my current contract today and a small improvement can be made.

Here is the original post for some more context.

Lee Dare - Software Development Principles, Plex/2e and everything else in between.: Exploring 2E messages via SQL (leedare-plex2e.blogspot.com)

In addition to the above, I recommend the SQL syntax is improved by catering for 'Mixed Case'

SELECT a.@@MSG, a.MSG, a.TYPOPT, b.SECLVL                     
FROM YMSGDTARFP a                                             
LEFT JOIN YMSGSECRFP b ON a.@@MSG = b.@@MSG                   
WHERE UPPER(a.MSG) like '%KD33%' OR                           
UPPER(a.TYPOPT) LIKE '%KD33%' OR UPPER(b.SECLVL) LIKE '%KD33%'

The actual query above is checking for the message name in the 2E model, the default message text and the second level text associated with the message.

Thanks for reading and as always, interested in any feedback.

Lee.

This doesn't get old.

More than 30 years after first seeing this, I appreciate it more and more.  It's like a fine wine maturing quietly in a cellar.

Lee.

Exploring 2E messages via SQL

Bitesized post today.

I needed to query the model to see if a particular function was called in second level message text.  I also wanted to see if it was referenced in the message name or the message text just to make sure I tracked down all usages.

The simple solution was to join the two files from the 2E model and do a little check to see if my string (PGM in this instance) was mentioned.

SELECT a.@@MSG, a.MSG, a.TYPOPT, b.SECLVL FROM YMSGDTARFP a LEFT JOIN YMSGSECRFP b ON a.@@MSG = b.@@MSG WHERE a.MSG like '%MYSTRING%' OR a.TYPOPT LIKE '%MYSTRING%' OR b.SECLVL LIKE '%MYSTRING%'                   

For more on the underlying files in 2E, take a look at these posts from many years ago.

https://leedare-plex2e.blogspot.com/search/label/model%20files

Thanks for reading.
Lee.

p.s. Remember to have your library list pointing to the correct model!

2E Model File II

I've made some minor changes to the model files ERD.

https://drive.google.com/file/d/0B4YRRM3roIs1cF9LTUtSR2dpQVU/view?usp=sharing


Thanks for reading.
Lee.

2e Model Files

Way back in 1991 a guy called Steve Hinzmann did a talk at a Synon user group.

I've never met the chap and have no idea where he is now and what he is up to.

However, I did discover his document many years later and it has been invaluable for learning the underlying data model of the 2E product.  This has allowed me to build numerous tools and utilities at various sites and assist other developers with queries based over the 2E model database.  Connect with me (somehow) if you want me to send you a copy of the presentation.

I've shared the document to all and sundry over the years but every time, I say "x page is outdated", it "doesn't cover arrays, triggers" and lots of other features that have made it into the 2E product since version 3.  The fundamentals are solid though!!!

Recently I had a bit of spare time so I decided to document the 2E model (ERD) to assist a colleague whilst they were exposing a data dictionary.  I also built lots of queries to help us ensure our standards were being followed thereby refreshing myself of the nuances within 2E model.



The document can be downloaded from here.

https://drive.google.com/file/d/0B4YRRM3roIs1QndYQW9ieW0tMGs/view?usp=sharing

This is a fluid document and there may be an error or two.  Let me know and I'll keep this up to date.  Please share if you feel others will get some benefit from it.

Update:AUG 2017

https://drive.google.com/file/d/0B4YRRM3roIs1cF9LTUtSR2dpQVU/view?usp=sharing

Steve's document is a lot more detailed and is a must read for anyone wanting the get to grips with the underlying files etc.  One day I may have a little more time to update this also :-)

Thanks for reading.
Lee.