DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message
sensiva



Group memberships:
Premium Members

Joined: 22 Aug 2017
Posts: 17

Points: 323

Post Posted: Mon Sep 23, 2019 6:08 am Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Parallel
OS: Unix
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



Group memberships:
Premium Members

Joined: 03 Aug 2011
Posts: 2873
Location: USA
Points: 21812

Post Posted: Tue Sep 24, 2019 6:27 am Reply with quote    Back to top    

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
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 43026
Location: Denver, CO
Points: 222086

Post Posted: Tue Sep 24, 2019 6:42 am Reply with quote    Back to top    

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

Peaches come from a can, they were put there by a man
If I had my little way, I'd eat peaches every day
Rate this response:  
Not yet rated
stuartjvnorton
Participant



Joined: 19 Apr 2007
Posts: 525
Location: Melbourne
Points: 3904

Post Posted: Tue Sep 24, 2019 9:22 pm Reply with quote    Back to top    

Image
Rate this response:  
Not yet rated
ray.wurlod

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 54537
Location: Sydney, Australia
Points: 295725

Post Posted: Tue Sep 24, 2019 9:39 pm Reply with quote    Back to top    

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.
Rate this response:  
Not yet rated
sensiva



Group memberships:
Premium Members

Joined: 22 Aug 2017
Posts: 17

Points: 323

Post Posted: Wed Sep 25, 2019 2:14 am Reply with quote    Back to top    

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:
 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
Rate this response:  
Not yet rated
UCDI



Group memberships:
Premium Members

Joined: 21 Mar 2016
Posts: 377

Points: 3885

Post Posted: Wed Sep 25, 2019 12:10 pm Reply with quote    Back to top    

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).
Rate this response:  
Not yet rated
sensiva



Group memberships:
Premium Members

Joined: 22 Aug 2017
Posts: 17

Points: 323

Post Posted: Thu Sep 26, 2019 2:21 am Reply with quote    Back to top    

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
Rate this response:  
Not yet rated
stuartjvnorton
Participant



Joined: 19 Apr 2007
Posts: 525
Location: Melbourne
Points: 3904

Post Posted: Sun Sep 29, 2019 1:15 am Reply with quote    Back to top    

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.
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 43026
Location: Denver, CO
Points: 222086

Post Posted: Sun Sep 29, 2019 7:01 am Reply with quote    Back to top    

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

Peaches come from a can, they were put there by a man
If I had my little way, I'd eat peaches every day
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours