Another tool to the list - Outsystems?

My New Year resolutions:-  

  1. Learn some new stuff.
  2. Finish my personal projects (Arcade, game development and writing).
  3. Try and get better at golf!
Let's kick off #1 :-)

Revisiting Outsystems: Free Developer Tier and Familiar Parallels

I’ve been experimenting with Outsystems on and off for a while.

  • First tried it back in 2019.

  • Revisited in March 2025.

  • Challenges: environments only lasted 10 days (though there were workarounds), and everything was in a shared space—meaning my work was visible to other newcomers. At one point I even accidentally updated someone else’s tutorial models!

Hot News

As of October 1, 2025, Outsystems now offers a 100% free developer tier that doesn’t expire. Even better, it’s your own private environment—not shared. As long as you revisit it periodically (no exact timeframe specified), your training instance will remain available.

This feels like the right time to revisit the technology, both personally and professionally.

Pricing Caveat

Once you move beyond the free tier, Outsystems is priced for enterprise use. The entry point is around $36k, with additional costs for resources, capacity, user counts (internal and external), and multiple applications. Think of it as a pricing model similar to Salesforce. If you’re coming from Synon or Plex, the structure will feel familiar.

Observations While Exploring

1. Community Energy The Outsystems community reminds me of the old Synon days in the mid-90s. Take a look at the conference highlights reel. I remember attending Synon Europe in London around 1997, with Vigil on stage talking about advanced patterns. The energy in the room was incredible, and it looks like Outsystems developers are experiencing that same buzz today.

2. Architectural Parallels The concepts, approach, and terminology in Outsystems development tools feel very familiar to anyone from the 2E and Plex ecosystems. I’d even hazard a guess that there’s some crossover of ideas. You can see more in their evaluation guide.

Looking Ahead

I’ll continue exploring Outsystems and share direct comparisons to Synon 2E and Plex where appropriate. Some tutorials (like the Horse Racing Model) aren’t available, but overall I’m impressed with what I’ve seen so far.

Subscribe to the blog for updates, or follow the Outsystems label. After this, I may also look at other low-code platforms—Appian, Salesforce, Mendix—and see how those ecosystems align.

Thanks for reading the
Synon 2E and Plex Emporium by Lee Dare (A developer blog). 
Lee.

👉 Have you tried Outsystems or another low-code platform? Share your experiences in the comments—I’d love to hear them.

- No comments:
Labels: ,

CA Plex - Model API related posts

CA Plex - Model API related posts


Quite a popular series of posts on this blog were to code with the CA Plex Model API

To make things a little easier, this post summaries all the related posts.


Model API Overview


Using the Model API in a c# project (Tutorial)


Entity Creator project.  What I developed with this knowledge.

Thanks for reading the
Synon 2E and Plex Emporium by Lee Dare (A developer blog). 
Lee.
- No comments:
Labels: , , ,

Synon 2E Internals - Steve Hinzmann Synon User Group 1991

Synon 2E internal file structure

I have a few posts (see below) where I discuss the Synon 2E internal files.


This all stems from the awesome work presented by this legend, Steve!!!

Steve's guide 2e Model



OG presentation scan.



Warning query these files at your own peril!  However, this knowledge is awesome for things like generation of data dictionaries and source code pre-compilations.

Thanks for reading the
Synon 2E and Plex Emporium by Lee Dare (A developer blog). 
Lee.
- No comments:
Labels: , , ,

Useful synon 2E sites you MUST bookmark

Synon 2E related useful sites and links you MUST bookmark


This blog isn't the only useful place to get great Plex and Synon 2E content.

Here are some 'up to date' and 'active' links.  

These appear to be creating content largely from the 2E manuals.

The 2E manuals in their raw form from Broadcom.

The Broadcom forum.

Annual conference site from CM First.

If you want to modernise your Synon 2E applications or port.

A services company?

A lovely old post from the OG sales guy from Synon.

Some LinkedIn groups and their member count as of 06/12/2025.
https://www.linkedin.com/groups/3307655/ - Synon Professional (417)
https://www.linkedin.com/groups/1637887/ - Synon Programmers (1880)

I'd appreciate a reciprocal link for my site if you are the owners of any of these.  It helps with google discovery. :-)

Any other links to share, please get in touch.

Thanks for reading the
Synon 2E and Plex Emporium by Lee Dare (A developer blog). 
Lee.
- No comments:
Labels:

Synon 2E - Technical Deep Dives

Synon 2E - Technical Deep Dives

Below are some long form posts targeting specific areas of the Synon 2E tech stack.

I thoroughly enjoyed writing these 'back in the day' and hope that they help some of you.

The generic data driver is a method to output *Arrays into PRTFIL's or DSPFIL's without having to define a structure file etc.  Very, very useful and I have used numerous times.  The only design consideration is the array size depending on which RPG generator is being used. 


Performance tuning batch jobs?


Pet hates?

Some things that I don't like seeing in 2E coding.  Call them standards?, call them being a fusspot?

Code reviews, love them or hate them, they share knowledge and create better developers.

General Synon 2E topics, hints, tips, 'How to' guides.  They'll be a gem or two in here for most of you.

This should be standard in all shops

Nice little tips here

A personal favourite

Really useful, especially with the new webservices push and unpack and package up input and output arrays.

Thanks for reading the
Synon 2E and Plex Emporium by Lee Dare (A developer blog). 
Lee.
- No comments:
Labels: , , , , , , , ,

Synon 2E - SQL and Model files summary

Synon 2E - SQL and Model files summary


Some of the SQL posts over the last year or so.  Some useful 2E model files extraction routines, audit stamp updates and some general IBMi knowledge.


Want to get down and dirty into the inner depths of the 2E model files?  These posts will help give you the confidence to dive in.

WARNING: Not for the faint hearted and welcome to surrogate hell (or is it heaven).


Thanks for reading the
Synon 2E and Plex Emporium by Lee Dare (A developer blog). 
Lee.
- No comments:
Labels: , , , , ,

Synon 2E - Standards posts all in one page

Synon 2E - Standards posts all in one page


Many of these were written years ago and google is gradually forgetting them.

Here is a summary of some of the technical posts I've made with lots of helpful hints, tips and development standards for Synon 2E, CA 2E and now Broadcom 2E.

This one is useful if you want to write performance code of reduce generator code bloat
https://leedare-plex2e.blogspot.com/2008/07/2e-development-standards-performance.html

Some general 2E coding standards


Some coding standards around action diagramming and usage of contexts
https://leedare-plex2e.blogspot.com/2008/08/2e-development-standards-ad-contexts.html
Naming Conventions?

More hints and tips, diving a little deeper into certain function
https://leedare-plex2e.blogspot.com/2008/10/2e-development-standards-hints-and-tips.html
Standards and hints for CHGOBJ/CRTOBJ composite functions

Screen function types and some standards to assist with solid development
https://leedare-plex2e.blogspot.com/2009/01/2e-development-standards-screen.html
Little tip with using cursor with subfiles
https://leedare-plex2e.blogspot.com/2019/01/a-little-trick-with-set-cursor.html

Thanks for reading the
Synon 2E and Plex Emporium by Lee Dare (A developer blog). 
Lee.

- No comments:
Labels: , , , , , ,

The Worst Word in the Workplace

The Worst Word in the workplace. An AI reprocessed legacy post

I wrote a post about the word 'resource' many years ago.  

https://leedare-plex2e.blogspot.com/2016/10/feeling-resourcefulnot.html

Today for a little bit of fun, I asked 'co-pilot' to refactor it, add humour and energy.  This was the result!  Quite impressive if you ask me.

Thanks for reading and don't bother reading the original as 'Co-pilot' did way better!

Lee.


Let’s get one thing straight: my most hated word at work is not f$%k, tosser, w&^%er, or even the dreaded “c-word” (and no, I don’t mean Christmas). Those are fine in context — usually shouted across a pub table after someone’s nicked your chips.

The word that makes me twitch, the one that should be banished from every office, is this:

Resource.

“We don’t have the resource.” “I’ll check the resource schedule.”

Every time I hear it, a little part of my soul packs its bags and heads for the door.

Why “Resource” Is Rubbish

Calling people “resources” is corporate code for: you’re replaceable, interchangeable, and about as personal as a stapler.

Once upon a time, companies had Personnel Departments — actual humans looking after actual humans. Then came the Americanisation: Human Resources. That was the moment we stopped being people with skills and started being reference numbers with salary ceilings.

And if your company still talks about you as a “resource”? Time to polish your CV, or at least stand up and say: No thanks, I’m not coal.

People ≠ Commodities

Let’s remember what a resource really is: coal, sugar, soybeans, oil, cocoa, tin, steel. All fine words for things you can burn, melt, or trade on an exchange.

But last time I checked:

  • Coal hasn’t written a single C# function.

  • Sugar hasn’t debugged a parsing algorithm.

  • Soybeans haven’t offered counselling.

  • Oil hasn’t built a school.

Humans do those things. Humans with talent, experience, and the kind of domain knowledge you can’t just order off Amazon Prime.

The IT Angle

I work in software engineering. Sure, you need technical chops — databases, languages, platforms. But you also need business knowledge, context, and the ability to translate messy real-world problems into clean code.

Thinking you can pluck someone off the street and have them instantly productive on a complex system? That’s not “resource planning.” That’s fantasy football.

Better Words Exist

Open a thesaurus and you’ll find words that actually respect people:

  • Ability

  • Capability

  • Talent

These words recognise skill. They acknowledge contribution. They don’t reduce you to a consumable item that can be replenished like printer paper.

My Crusade

Project managers love “resource.” Middle managers (especially the 6ft 5” ones with clipboards) practically bathe in it. But every time I hear it, I cringe.

So I’ve made it my mission: wherever I work, I beat the drum to eradicate this corporate cancer. Replace it with Capability, Skills, Staffing, Talent, Capacity — anything that reminds us we’re humans, not coal.

Final Word

If you’re reading this and still calling your colleagues “resources,” stop. Unless you genuinely believe Dave from Accounts is interchangeable with a sack of soybeans.

Words matter. Use better ones.

Moan over.

Thanks for reading the
Synon 2E and Plex Emporium by Lee Dare (A developer blog). 
Lee.
- No comments:
Labels: , ,

Google was hiding the site

Google was hiding the site


[Edit: 05/09/2025 - I appear to still be getting search issues for the blog. Have tried getting it reindexed]

I am not 100% sure what happened, but during 2024 I had the need to search my blog site for a problem I was getting.  I knew I had solved and documented it on the site previously, after all, this the principle reason for writing up these posts i.e. share knowledge and maintain a library of information across employers/contracts.

However, when searching I noticed that the page I was expecting simply wasn't being returned.

I tried a few other browsers and had similar results (nothing).  DuckDuckGo however, worked!  At this point the penny dropped that somehow it was Google that was dropping my site and the other browsers were configured to use google as their search provider.

To this day I don't 100% know what was causing this issue, but the steps I worked through were.

1. Set the blogger site to not allow crawlers.

Blog settings


2. Generate a sitemap with a free 3rd party tool and upload to the root.

Blog Sitemap


3. Use google console to encourage reindexing.  This reduce the number of non indexed pages from over 200 to 44.  Note the 44 are mainly duplicates of the page within the google/blogger sphere so fortunately this means the content is still fully available.

Blog Stats


4. Be patient and respond to the many google emails, keep retrying indexing options after each YouTube video of influencer blog post and their recommendations that I read.

Google Emails

For now, this seems to be solved.

This issue may have been going on for weeks, months or even years.  I have no idea.  Hopefully, all now back online and searchable.

If you don't already, please bookmark if you find this blog of value so that you can continue to watch as new posts get up and running.

I had held back on some posts for early 2025 whilst I was trying to resolve the issues.

Thanks for reading the
Synon 2E and Plex Emporium by Lee Dare (A developer blog). 
Lee.


- No comments:
Labels: , ,

Did you know Synon 2E has a Full Screen Mode?

Long time IBMi users typically work with an expanded WRKACTJOB screen shortly after learning all the command keys that are of use.  As a junior developer (back in the early nineties), I was always in awe of the seniors around me who didn't need to toggle through the command key options whilst managing the jobs running in the system.


WRKACTJOB screen (normal)

As always though, once you had learnt the system you could press F21 to remove the subfile options and command key text, thus freeing up more room for the critical data on these somewhat limited 5250 displays. 

If I remember rightly, the command key used to bring up a window and you could chose between Basic (might have been intermediate) and Advanced mode.  This has long since been removed from the o/s and the command key merely acts as a toggle nowadays.  The reward of course was the ability to see more of your processes and interactive sessions.

Here is my current 'Pro' setup lol.


WRKACTJOB screen (Full Screen)


Synon aka 2e with various prefixes, Cool: Allfusion: Advantage: CA: etc also has the ability for a user to see more on selected screens.  Here are a couple that might be of interest. 

Action Diagram Expanded vs Normal view.


Synon action diagramming screen (Full Screen)

Synon action diagramming screen (normal)

Most developers could survive with this as virtually all worth their salt will know every option and command key off the top of their head anyhow.

A lesser-known scenario and also a bit harder to remember all the options, is the ability to extend the records shown when viewing a model list. 


Synon Model List screen (Normal)

Synon Model List screen (Full Screen)

Personally, I don't have this one switched on but kudos to anyone out there that does!

So how do you set these values?

From the main Services Menu take option 11 for Edit model profile (YEDTMDLPRF) or as the menu implies, execute the command.  Then set the options accordingly.


Synon Options Screen

You're welcome.
Thanks for reading the
Synon 2E and Plex Emporium by Lee Dare (A developer blog). 
Lee.

- No comments:
Labels: , , ,

They say a picture is a 1000 words!

A thousand?........... In this instance it is more like tens of thousands.

Anyhow, a colleague was talking about tag clouds the other day and it got me thinking.

"What would this blogs labels look like in a tag cloud?"

So......... I popped online and googled for a tag cloud generator and discovered this site that looked pretty good.

https://simplewordcloud.com/ or you can click here:  Simple Word Cloud Generator

After some Excel wizardry to generate the source data. Roughly translated as a screen scrape, removing some spaces, duplicating entries relative to the blog usage count, copying out the formatted data etc.

I excitedly 'cut n pasted' it into the input field on the website and clicked the button.

Results as below.

Tag summary for my blog

Thanks for reading the
Synon 2E and Plex Emporium by Lee Dare (A developer blog). 
Lee.
- No comments:
Labels: , ,

Synon 2E Understanding CONstant usages

UPDATE: 06/12/2025 - Linked to a summary post dedicated to deep dive long form posts.

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.

Action diagramming screen CON

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

Synon AD to RPG

Synon AD to RPG


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

How RPG handles CON (Constants)


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.

Synon action diagram database table

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.

Some synon 2E model files

What does the data look like in the table?

Synon CON (Constants)

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.

CON Usage query

I hope that this helps someone.

Thanks for reading the
Synon 2E and Plex Emporium by Lee Dare (A developer blog). 
Lee.

p.s. This remains one of my favourite posts :-)


- No comments:
Labels: , , , , , ,

Synon 2E Exploring messages via SQL (Update)

UPDATE: 06/12/2025 - Added a summary link post to aggregate these types of posts.

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 the
Synon 2E and Plex Emporium by Lee Dare (A developer blog). 
Lee.
- No comments:
Labels: , ,

Synon 2E It's (ELeM)entary, My Dear Watson

UPDATE: 06/12/2025 - Linked to a summary post dedicated to deep dive long form posts.


I've used this trick a few times so I thought I should share it on the blog before I get run over by a bus.

Often we (developers) are asked to produce or consume data files for integrations with 3rd party systems.  Even with the emergence of web services there are still many many times where the preferred method of interchange is file based, typically these are bulk (high volume) transactional exchanges.  Think payments between banks etc 

For the systems I've worked on we have solved this with .csv delimited inbound and outbound files.  We have also utilised fixed width files, the majority having old school multi-format layouts with a traditional header, detail and footer structure which has some advantages over .csv.

For now, I will also ignore that we could have also provided XML or JSON payloads as part of a real-time integration. 

As you can see, there are numerous ways to skin this cat. However, this is a 2E and Plex blog so for today, let us concentrate on:-

  • How you might build and consume files into a 2E based application. 
  • How can we create a multi-format flat file using purely Synon logic only?

Let's fire up a 5250 session and explore an unsung feature in 2E that helps remove the complexity of building these flat files.

What are we going to build? 


An extract file with a header record, 10 detailed transactional records and a footer which denotes both EOF as well as have a total  for basic validation.  In the real-word this may include hash total etc

An example of what this file data might look like is below.

File format Banking style?


Back in 2E, first define a file with one Known By (Numeric 9.0) should be suffice for most integrations and a second field (Has relation) of 'Flatfile Data' - Type TXT and length of 500 (or whatever length works for your environment). RP4 has better field limits than RPG. 

I've called my file FIXFILP.


Data file 2E sample


Now we need to build the data record, usually you would start concatenating the data values together to meet the design specification of the receiving system and handle those pesky FILLER57 fields etc that always appear from somewhere.

This involves dozens (if not hundreds) of lines of code to concatenate the data.  The resulting action diagram is often difficult to understand and cumbersome to maintain.

What if there was an easier way to build up the data with the ability to easily reorder the fields and cater for changing field lengths.  Well there is, using a neat unsung feature of 2E arrays.

2E keeps track of the last record to be added, changed or read in an array, a sort of cursor I guess.  This is available in action diagram logic hidden in the ELM context. (ELeMent).

The only 'built in' function that can use the ELM context is a *CVTVAR.  

First create an array with the data fields you would like to appear in the dataset, this can be header, detail 1, detail 2, footer etc.  It doesn't really matter for a flat file process.  To keep it nice and simple I have made up some generic fields names with various data types.


Parameters1


Parameters2



Parameters3

I've keyed these arrays based on the Record type of HDR, DTL and FTR.  You can do how best suits your use case.  All the arrays are set with a 'Numer of Elements' of 1 record as I don't need to use the in the traditional sense.  I just need a pointer to an ELM in memory.


Sample AD code

All we then do is call a CRTOBJ over the array to populate the data.  Once in the array, we can use the *CVTVAR to populate a flat file field.  2E handles all the different data types and spits out a well formatted string which you can write to the database/extract file etc

Detail Data

But we are not done.  I've ready other blogs that talk about ELM and they do a pretty good job of explaining the outbound example above.  But not many people realise that depending on whether you are using the ELM context as Input or Output, is the equivalent of constructing or deconstructing the data.  So yes, this method can be used to unpack flat files also. :-)

As long as in the receiving function you have created a shell array record.  You can use ELM to move the data into the array and then standard RTVOBJ functionality to retrieve the record in its deconstructed form.


AD Code

An example below of a couple of screens that I rushed together showing the data string and the subsequent fields.

DSPFIL screen

DSPRCD screen

You simply point the flatfile string to the correct array and 2E will handle everything else.

Thanks for reading the
Synon 2E and Plex Emporium by Lee Dare (A developer blog). 
Lee.


- No comments:
Labels: , , , , , , , ,

MODEL MAP - This doesn't get old.

Just flicking through a Synon 2E model and I must have pressed the wrong button and low and behold what pops up.  The Model Map screen for Synon 2E.  This doesn't get old does it!

Synon help screen


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.  It is amazing how seeing this screen makes me nostalgic for both ex-colleagues and technology.

Thanks for reading the
Synon 2E and Plex Emporium by Lee Dare (A developer blog). 
Lee.
- No comments:
Labels: , ,

Synon 2E Exploring 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 the
Synon 2E and Plex Emporium by Lee Dare (A developer blog). 
Lee.

p.s. Remember to have your library list pointing to the correct model!
- No comments:
Labels: , ,

Object Usage via SQL

Usually, I would use the Work with Object (WRKOBJ) command and work through them one by one capturing the data in excel or notepad manually.  Another option is to build a list of objects on the system using the OUTFILE keyword from the DSPOBJD command and then query these via SQL or Query/400 or simply by using YWRKF our trusted friend in the 2E world.

Today however,  I had a list of around 50 or so objects and as we have separate development and production machines (the latter, I have no access to via segregation of duties policies that I support BTW), I felt it was unfair to ask a colleague to send me 50 screen prints, as well as, this being prone to user error with so many commands to execute.

I could have documented the steps for the traditional method above but this isn't really repeatable for my colleagues nor is it enjoyable.  Therefore, I decide to write an SQL (or four) as it turns out to get me the data and leave us with a template for the future.  There is a bonus 5th one for us 2Er's

There is a bump in the road though.  Isn't there always aye!

Even though IBM have made huge strides with the availability of data via SQL in recent releases.  A simple view for the OBJECT_STATISTICS does not exist.  There is a table function which will get you the data but is precluded on obtaining data for an entire library or a subset based on object type. 

Here is an example straight from the IBM documentation.

https://www.ibm.com/docs/en/i/7.4.0?topic=services-object-statistics-table-function

When I applied this to my test library, I didn't want the timestamp but a date only and I wanted a subset of the fields available.

This is quite a simple modification to the IBM example, we just do a substring at take the first 10 characters and give the field as nice name.  Note also the replacement of the asterisk (*) for the select to exact fields.

SELECT OBJNAME, DAYS_00001, 

SUBSTR(VARCHAR(LAST_00001), 1, 10) AS LAST_USED

 FROM table                                    

(object_statistics('MYLIB', '*ALL'))   


Unfortunately, I hit a hurdle when trying to view the resulting data via YWRKF.  The 2E programs raised an error condition and threw a hissy fit as I had some null dates represented as '-' in the returned data and not the ISO format (0001-01-01) it was expecting.

A quick google later and an a minor adjustment to the SQL to present null as an empty ISO date and I was now able to view the data.  FYI, Query/400 was fine with the data, so this is an extra steps for loyal 2E users/shops like me/us.  There are also other date conversion routines readily available I just chose this method for now....

SELECT OBJNAME, DAYS_00001,                                         

IFNULL(SUBSTR(VARCHAR(LAST_00001), 1, 10),'0001-01-01') AS LAST_USED

FROM table                                                        

(object_statistics('MYLIB', '*ALL'))


This is all well and good, but I also wanted to restrict this SQL result to the objects that were of interest.  I achieved this by using a SQL and filter out those of interest.

SELECT OBJNAME, DAYS_00001,                                        

IFNULL(SUBSTR(VARCHAR(LAST_00001), 1, 10),'0001-01-01') AS LAST_USED

FROM table                                                        

(object_statistics('MYLIB', '*ALL')) a WHERE a.OBJNAME in      

('MYOBJ01', 'MYOBJ02', 'MYOBJ03', 'MYOBJ04', 'MYOBJ05',            

 'MYOBJ06', 'MYOBJ07', 'MYOBJ08', 'MYOBJ09', 'MYOBJ10')

SQL to extract object usage data on IBM I and incorporating some synon model queries for extra info.


UPDATE: 06/12/2025 - Added a summary link post to aggregate these types of posts.

Continuing the series on using IBM i SQL for some basic work management tasks.  Today I had a large list of objects that I wanted to understand the date that they were 'last used' and 'days used' count in order to determine if :-

1. The objects could be removed or flagged as obsolete.
2. Prioritise the development/testing based on high volume activity.


Whilst this is great it is only a view on the screen.  I didn't want my colleague to have to take screen prints or scrape the screen with tedious cut and paste to build an excel file for me.

CREATE TABLE QTEMP/OBJ_USAGE AS(
SELECT OBJNAME, DAYS_00001,
IFNULL(SUBSTR(VARCHAR(LAST_00001), 1, 10),'0001-01-01') AS LAST_USED
FROM table
(object_statistics('MYLIB', '*ALL')) a WHERE a.OBJNAME in
('MYOBJ01', 'MYOBJ02', 'MYOBJ03', 'MYOBJ04', 'MYOBJ05', 'MYOBJ06', 'MYOBJ07', 'MYOBJ08', 'MYOBJ09', 'MYOBJ10')
) WITH DATA

This was great for my purposes, I sent the SQL to a colleague, and they were able to implement it and send me a file back.  Job done.

Hold Tight!! 

This is a 2E blog (mainly) and I have only mentioned YWRKF.  You can do better than that I hear you cry.

The above list of objects can easily be expanded or substituted with a list from a file which you could build somehow.  What if I linked this to a Model List?  That would be cool right!

Whilst this isn't an option for my site due to having separate machines, it might work for you.  If you are like us, you may have send over some data periodically from production and work on the queries from a different angle.  Anyhow, assuming you have model lists and objects you wish to query on the same machine you can embed your SQL in RPG, CL or as a SQL source member and run with RUNSQLSTM etc.

You just need to link the Model list with your library of objects.  See below for one method.

We create an alias of the member we wish to use as our list.

CREATE OR REPLACE ALIAS QTEMP/OBJ_USAGE FOR MYMODEL/YMDLLSTRFP(MYLIST)

Execute the query.  Optionally as above, you can output to a file if you wish.

SELECT OBJNAME, DAYS_00001, IFNULL(SUBSTR(VARCHAR(LAST_00001), 1, 10),'0001-01-01') AS LAST_USED
FROM TABLE (OBJECT_STATISTICS('MYLIB', '*ALL')) a LEFT JOIN QTEMP/OBJ_USAGE a ON a.OBJNAME = b.IMPNME WHERE  b.OBJTYP = 'FUN'

Lastly, tidy up after ourselves and drop the temporary ALIAS.

DROP ALIAS QTEMP/OBJ_USAGE

As always, I am sure that there are other ways of solving this problem and I would love to hear about them in the comments.  This is my current 'new method' and will likely change as I notice more and more flaws or need to expand the scope.

Thanks for reading the
Synon 2E and Plex Emporium by Lee Dare (A developer blog). 
Lee.
- No comments:
Labels: , , , ,
Subscribe to: Comments (Atom)