Quick fix in Prod - Before / After statements

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
sensiva
Premium Member
Premium Member
Posts: 21
Joined: Tue Aug 22, 2017 10:39 am

Quick fix in Prod - Before / After statements

Post by sensiva »

Hello,

I have a question which i have been thinking for a while, how others would have implemented, or is there any best practise .

Its been 2 years for me in datastage and thanks to this forum which helped me gain a good amount of knowledge. I have worked across 4-5 projects in these 2 years and all these projects have a common solution put i place for a quick work-around in production environment.

All the projects, do their development as per the business need, properly tested and after all good work they move to production. And in production every sequence is followed by a quick fix sequence. so if we have 50 sequences that actually does the business logic, then we will have 100 sequences called by the scheduler.

The quick fix sequence, helps to quickly fix the problem in production if any, they select a predefined sqlprocedure as text from a table and then execute them. I agree that it helps in certain places, but in most of the cases people just make them grow as a real functional sequence and in some of our projects, we have more then 1000 plsql procs executed before or after an actual functional sequence.

I was wondering if there could be a dynamic way of using the before/after statements in oracle connector. May be while developing assign a predefined value for each connector, or in the sequence have a first job that gets the queries required and then pass it to the other jobs as parameters to be executed for before or after statements etcc...

I agree that the question is more process oriented than being technical, but wondering how others face these kind of needs.

Thanks
Sen
sen
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

It's fine to ask a process question. I don't know the answer though. I really don't know the nature of it so I am imagining if we had that type of scenario in our environment...

It sounds like a clever way for allowing people to alter data directly in production without doing the harder work of planning better and implementing long term solutions in the main sequence of ETL jobs. In our environment I am 99% sure that would be viewed as a security violation and therefore is not allowed.

I would suggest taking a step back and look at the big picture. That stored procedure "business" (or nonsense?) should not be necessary. What went wrong over the life of that project... What is the solution? Is a data quality and cleansing effort needed? Is more time needed up front to gather better requirements? Is more detailed data analysis needed?
Choose a job you love, and you will never have to work a day in your life. - Confucius
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

I'm of much the same mind as qt_ky - both in not having any kind of answer right off the bat and also with much the same questions. And I'm really curious what kind of "quick fix", production or otherwise, that can be handled by anything before or after sql statements. I've never found myself needing to do anything like that after a crap ton of years doing ETL using multiple tools. And our entire current warehouse project has less than a half-dozen stored procedures that it leverages.

Our process would not allow anything like what your "quick fix sequence" sounds like... expect perhaps in an emergency situation when it would only be in place until an expedited fix would work its way up the food chain. In the last 10 years I've only seen that exactly one time.

I for one will be waiting to see how the questions being asked are answered to try to get an idea what is driving this need in your environment.
-craig

"You can never have too many knives" -- Logan Nine Fingers
stuartjvnorton
Participant
Posts: 527
Joined: Thu Apr 19, 2007 1:25 am
Location: Melbourne

Post by stuartjvnorton »

Image
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

I agree with what Stuart said.

That having been said, pretty much every property in a Connector stage can have its value supplied via a job parameter reference. Think on that.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
sensiva
Premium Member
Premium Member
Posts: 21
Joined: Tue Aug 22, 2017 10:39 am

Post by sensiva »

Thanks for sharing your views, I am completely in line with your comments and would really like to change the situation here in my projects. But want the transition to be smooth by still allowing them some flexibility rather than putting an big end to this process immediately, Hence looking for different possibilities.

Let me give you an example of how these requirements arrive.

Code: Select all

 We are a leading multinational enterprise and hence the need to manage multiple zones and locations for different types of products are there. All these are well captured during business requirement and the development done accordingly and goes to prod. After a year or so, say one of the location starts selling/manufacturing a new product for which the availability date or end of support dates are not known yet. Hence application A which is supposed to provide the dates, sends null now and Application B which definitely needs the date aborts. Application B is impacted for no issue of their own, and Application A is not so much worried about it.

The support team of application B knows the fix, that if the date is null then they got to update that to some date in 2099 so as to not take that product into account for other calculations. With the fix being simple and that all datastage developers are busy with other projects, it could easily take a month or even 2 to move this small fix to prod with the actual process in place where-as with this quick fix sequence, the support team they themselves manage the fix and no aborts from next day.
This was a small example, in some case, they even write a big plsql proc to be executed.

Would update here if i have any interesting solution. All i have in mind currently is to change the quick fix sequence not to execute queries which are more than 2 months old......

Also thinking of removing these additional sequence and integrate this functionality to the actual job/sequence... Lets see..

Thanks
Sen
sen
UCDI
Premium Member
Premium Member
Posts: 383
Joined: Mon Mar 21, 2016 2:00 pm

Post by UCDI »

I would say this is a security problem. One can argue that datastage itself is a security problem though.. a run command stage has near root, and you can always find a piece of a job to hijack that can do something inappropriate. You can even get the logs to show your passwords, and steal datastage's ID.

I would just say, this should be run by the security team at your company if you have one. It sounds like added risk (a novice can do damage, a mad person can do damage, a crook can steal data, to name a few immediate concerns with this).
sensiva
Premium Member
Premium Member
Posts: 21
Joined: Tue Aug 22, 2017 10:39 am

Post by sensiva »

I had a meeting with the Business and the support even yesterday on this point and they are asking to provide a solution that could resolve the prod issue within the SLA time of 4hrs in case of a Sev 1.

Though being a batch process, our application is highly critical and directly inclined with the business each day. Hence from their point of view, it looks a fair option to have in place.

I did tell them that this kind of option is not available in any other kind of integration method (real-time or api, etc) or even with batch modes that does not involve databases. How does these applications manage sev1? though no big responses, they still stand on the point, when we have an option, why not utilize it.

Let me keep you posted if any improvements...
sen
stuartjvnorton
Participant
Posts: 527
Joined: Thu Apr 19, 2007 1:25 am
Location: Melbourne

Post by stuartjvnorton »

Yeah, that argument is a slippery slope to nowhere good.

The business don't care about proper IT dev practices, that's not their job. Expect them to push for the most expedient thing they can get away with.
It's up to IT leadership to define and defend the integrity of their environment and practices.

This sort of stuff should definitely be the exception. Don't give the business the tools to make it the norm. The last thing you do if you want to phase something out is to give people a much easier way of doing it...

I have seen in SAP environments the concept of "Firefighter" logins, where things like this can be done as a *once-off*, and *everything* is logged and reviewed.

But if you're doing the same thing every time or multiple times, it's not a clean-up: it's fixing a bug or oversight in the existing job. Do it properly.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

I know this is a gross over-simplification of the issue but it sounds to me that your two systems need to integrated... or perhaps more better integrated. Cannot changes in one that are known to effect the other be propagated over and applied automatically, perhaps even in something approaching "real-time"?

Your quoted requirements don't really sound like bugs to me per se, hence the question.

Edited to add: We have systems like that. Our Enterprise Scheduler (Control-M) is leveraged where we can to keep systems in sync. We also have some with "shared stored procedures" that both can call so that one is always aware of what the other is up to for processes that need that.
-craig

"You can never have too many knives" -- Logan Nine Fingers
Post Reply