Kubilay Çilkara

Subscribe to Kubilay Çilkara feed
Database Systems is a blog about Databases, Oracle, Salesforce and Data IntegrationKubilay Tsil Karahttps://plus.google.com/103901222720404137805noreply@blogger.comBlogger128125
Updated: 18 hours 8 min ago

Before Investing in Data Archiving, Ask These Questions

Tue, 2016-09-13 15:39
Data has a way of growing so quickly that it can appear unmanageable, but that is where data archiving shines through.  The tool of archiving helps users maintain a clear environment on their primary storage drives.  It is also an excellent way to keep backup and recovery spheres running smoothly.  Mistakes and disasters can and will happen, but with successful archiving, data can be easily recovered so as to successfully avert potential problems.  Another benefit to archiving data is that companies are actually saving themselves the very real possibility of a financial headache!  In the end, this tool will help to cut costs in relation to effectively storing and protecting your data.  Who doesn’t want that?  It’s a win-win situation for everyone involved!

When It Comes to Archiving Data for Storage, Where Do I Begin?
It may feel stressful at first to implement a data archiving plan into your backup and storage sphere, but don’t be worried!  To break it down, data simply needs to go from one place into another.  In other words, you are just moving data!  Sounds easy enough, right?  If things get sticky however, software is available on the market that can help users make better decisions and wise choices when it comes to moving your data.  In your preparation to incorporate archiving, you must ask yourself some important questions before you start.  This way, companies will be guaranteed to be making choices that meet the demands of their particular IT environment.  So, don’t stress-just be well prepared and ask the right questions!  Right now, you may be wondering what those questions are.  Below you will see a few of them that are most necessary to ask in order to guarantee your archiving success. 

What You Should Be Asking: 

1.      With respect to your data, what type is that you need to store?  “Cold” data may not be something you are familiar with, but this is a term used regarding data that has been left untouched for over 6 months.  If this is the type of data you are trying to store, good for you!  This particular type of data must be kept secure in the event that it is needed for compliance audits or even for a possible legal situation that could come up down the road.  At any rate, all data -regardless of why it is being stored- must be taken care of appropriately so as to ensure security and availability over time.  You can be assured you have invested in a good archiving system if it is able to show you who viewed the data and at what time the data was viewed. 

2.     Are you aware of the particular technology you are using?  It is essential to realize that data storage is highly dependent upon two factors:  the hardware and the media you are using.  Keep in mind that interfaces are a very important factor as well, and they must be upgraded from time to time.  A point to consider when storing data is that tape has an incredibly long life, and with low usage and proper storage it could potentially last for 100 years!  Wow!  However, there are arguments over which has the potential to last longer...tape or hard disk.  Opponents towards the long life of tape argue that with proper powering down of drives, hard disk will actually outlive tape.  Here is where the rubber meets the road to this theory.  It has shown to be problematic over time, and can leave users with DUDL (Data Unavailable Data Loss).  This problem IS as terrible as it sounds.  Regardless of the fact that SSD lack mechanical parts, their electronic functions results in cell degradation, ultimately causing burnout.  It is unknown, even to vendors, what the SSD life is, but most say 3 years is what they are guaranteed for.  Bear in mind, as man-made creations, these tools will over time die.  Whatever your choice in technology, you must be sure to PLAN and TEST.  These two things are the most essential tasks to do in keeping data secure over time!

3.     Do you know the format of your data?  It is important to acknowledge that “bits” have to be well-kept in order to ensure that your stored data will be usable over the long run.  Investing in appropriate hardware to accurately read data as well as to interpret it is essential.  It is safe to say that investing in such a tool is an absolute MUST in order to ensure successful long-term storage!

4.     Is the archive located on the cloud or in your actual location?  Did you know this era is one in which storing your data on the cloud is a viable means of long-term archiving?  Crazy, isn’t it!  Truth be told, access to the cloud has been an incredibly helpful advancement when it comes to storage, but as all man-made inventions prove, problems are unavoidable.  Regarding cloud storage, at this point in time, the problems arise with respect to long-term retention.  However, many enjoy the simplicity of pay-as-you-go storage options available with the cloud.  Users also like relying upon particular providers to assist them with their cloud storage management.  In looking to their providers, people are given information such as what the type, age, and interface of their storage devices are.  You might be asking yourself why this is so appealing.  The answer is simple.  Users ultimately gain the comfort of knowing that they can access their data at anytime, and that it will always be accessible.   At this point, many are wondering what the downsides are of using the cloud for storage.  Obviously, data will grow over time, inevitably causing your cloud to grow which in turn will raise your cloud storage costs.  Users will be happy to know however, that cloud storage is actually a more frugal choice than opting to store data in a data center.  In the end, the most common compliment regarding cloud storage is the TIME involved in trying to access data in the event of a necessary recovery, restore, or a compliance issue.  Often times, these issues are quite sensitive with respect to timing, therefore data must be available quickly in case of a potential disaster.   

5.     What facts do you know about your particular data?  There is often sufficient knowledge of storage capacity, but many are not able to bring to mind how much data is stored per application.  Also, many do not know who the owner of particular data is, as well as what age the data may be.  The good news is that there is software on the market that helps administrators quickly figure these factors out.  This software produces reports with the above information by scanning environments.  With the help of these reports, it can be quickly understood what data is needed and in turn, will efficiently archive that data.  All it takes is a push of a button to get these clear-cut reports with all the appropriate information on them.  What could be better than that?
In Closing, Companies MUST Archive!


In a world that can be otherwise confusing, companies must consider the importance of archiving in order to make their data and storage management easier to grasp.  Maybe if users understood that archiving is essential in order to preserve the environment of their IT world, more people would quickly jump on board.  The best news is that archiving can occur automatically, taking the guesswork out of the process.  Archiving is a sure step to keeping systems performing well, budgets in line, and data available and accessible.  Be sure to prepare yourself before investing in data archiving by asking the vital questions laid out above.  This way, you can be certain you have chosen a system which will fully meet your department needs!

Writer Jason Zhang is the product marketing person for Rocket Software's Backup, Storage, and Cloud solutions.
Categories: DBA Blogs

Finding the Right Tools to Manage Data in Today’s Modern World

Tue, 2016-09-13 15:37
A common concern among companies is if their resources are being used wisely.   Never before has this been more pressing than when considering data storage options.  Teams question the inherent worth of their data, and often fall into the trap of viewing it as an expense that weighs too heavily on the budget.  This is where wisdom is needed with respect to efficient resource use and the task of successful data storage.   Companies must ask themselves how storing particular data will benefit their business overall.  Incorporating a data storage plan into a business budget has certainly proven to be easier said than done.  Many companies fail at carrying out their desires to store data once they recognize the cost associated with the tools that are needed.  You may be wondering why the failure to follow through on these plans is so common.  After all, who wouldn’t want to budget in such an important part of company security?  The truth of the matter is that it can all be very overwhelming once the VAST amount of data that actually exists is considered, and it can be even more stressful to attempt to manage it all.

When considering what tools to use for management of one’s data, many administrators think about using either the cloud or virtualization.  Often times during their research, teams question the ability of these two places to successfully house their data.  Truth be told, both the cloud and virtualization are very reliable, but are equally limited with respect to actually managing data well.  What companies really need is a system that can effectively manage huge amounts of data, and place it into the right categories.  In doing so, the data becomes much more valuable. 

Companies must change their mindsets regarding their stored data.  It is time to put away the thoughts that data being stored is creating a money pit.  Many times, people adopt an “out with the old, in with the new” philosophy, and this is no different with respect to storing old data.  The truth is, teams eventually want new projects to work on, especially since some of the old data storage projects can appear to be high maintenance.  After all, old data generally needs lots of backups and archiving, and is actually quite needy.  These negative thoughts need to fade away however, especially in light of the fact that this data being stored and protected is a staple to company health and security.  Did you know that old and tired data actually holds the very heart of a company?  It is probable that many forget that fact, since they are so quick to criticize it!  Also notable is the fact that this data is the source of a chunk of any given business’s income.  Good to know, isn’t it?!  Poor management of data would very likely be eradicated if more teams would keep the value of this data on the forefront of their minds.  It is clear that the task of managing huge amounts of company data can be intimidating and overwhelming, but with the right attitude and proper focus, it is not impossible. 

The best news yet is that there are excellent TOOLS that exist in order to help companies take on the magnanimous task of managing company data.  In other words, administrators don’t have to go at it alone!  The resources out there will assist users with classifying and organizing data, which is a huge relief NOT to have to do manually.  When teams readily combine these tools with their understanding of business, they have a sure recipe for success when it comes to management.  To break it down even further, let’s use a more concrete example.  Imagine you are attempting to find out information about all the chemical engineers in the world.  You must ask yourself how you would go about doing this in the most efficient manner.  After all, you would clearly need to narrow down your search since there are more than 7 billion individuals that exist on this planet.  Obviously, you wouldn’t need to gather information on every single human being, as this would be a huge time waster.  On the contrary, to make things more streamlined, you would likely scope out and filter through various groups of people and categorize them by profession.  Maybe you would consider a simple search in engineering graduates.  The above example in organizing information is probably the method most people use to organize simple things in their life on a daily basis.  Keep in mind, having these tools to help streamline data is one thing, but one must also possess a good understanding of business plans, as this will assist for a better grasp on corporate data.  With these things in line, businesses can be assured that their data management systems will be in good hands and running smoothly. 

What a great gift to be alive during this time, with the easy access to modern tools which help make management of data much more understandable to those in charge.  These valuable resources also help to create confidence in administrators so that they feel well-equipped to navigate the sometimes harsh IT world.  For example, during the changes with data types and volumes, these tools assist in helping to lower the storage capacity needs, lower costs on backup and recovery, and aid in the passing of compliance with flying colors.  In fact, many in charge can rest their heads at night knowing their companies are totally in line with modern-day regulatory compliance rules. 


In the end, the value of wisdom with respect to making various decisions in an IT department is incredibly important.  Arguably one of the most areas which this applies to is in the sphere of data management.  The truth is, dependence upon tools alone to navigate the sometimes rough waters of the IT world will not be enough to get teams through.  As stated above, wisdom and the right attitude regarding data and its importance to company health and security are vital to proper management.  In addition, clients ought to be looking for resources to assist them with organizing and classifying their systems of files.  The point is clear:  intelligence paired with the proper tools will give companies exactly what they need for efficient and effective data management.  At the end of the day, users can rest easy with the knowledge that their data -which is the bread and butter of their companies- is in good hands.  What more could you ask for?

Writer Jason Zhang is the product marketing person for Rocket Software's Backup, Storage, and Cloud solutions.
Categories: DBA Blogs

The Top 4 Ways to Handle Difficult Backup Environments

Thu, 2016-08-11 16:35
It may come as a surprise to you, but the administrators of backup, networks, and systems are essentially the backbone of the IT world.  Did you know that these heroes are responsible for some very difficult tasks?  These tasks include -but are not limited to- keeping critical business data secure and up-to-date, getting more out of existing hardware, and keeping auditors happy.  Overall, they are the ones who keep the whole sphere in line. 

In recent days, however, the jobs of these individuals has changed quite a bit.  Virtual tape libraries (VLTs), virtual machines (VMs), and additional technologies with respect to backup have made the job of a Backup Administrator much more complicated.  Also, there is more to be managed when corporate acquisitions occur.  With the addition of the fact that all departments want special reports that communicate factors most relevant to them, and that finance departments want each sphere to pay for their own storage, it seems that administrators have a lot on their plates!  The truth is, the plates of most Backup Administrators are full, and we haven’t even touched on compliance reports yet! 

On the upside, most Backup Administrators are well-equipped to handle the large load of work now required of them.  However, they are still human, and that makes them limited in terms of how much time they can spend in a particular area.  For instance, because of the list of tasks mentioned above, everything takes longer.  What this means is that less time can be spent on management of the entire backup sphere.  This should not surprise anyone, as even administrators can only do so much!  The good news is that there is light at the end of the tunnel.  If you are in a situation where you have too many proverbial pots on the stove with respect to your backup environment, don’t worry!  Here are 4 essential tips that will help you to wrangle in those testy backup spheres. 

1.     Create a client backup status every day.
You must remember the importance of making the backup status of your clients clear on a daily basis.  In order to do this, figure out the job information to use as your base.  You can depend upon your applications to supply indicators that make this easier. Next, you must consider your backup window.  Typically, you will see something like 7pm to 7am, meaning that the status of your daily backup doesn’t follow the calendar day.  Bear in mind the reality of missed jobs and that everything might be communicated as “ok” because of no job to report.  However, the truth is, this missed job ought to be marked as “missed.”  This can be done by checking on scheduler rules.  In the event of an external scheduler, this data needs to be associated with the client data in the backup product.  In the end, you must decide on how you want to handle the load of many jobs.  It is important to ask yourself how you would view failed jobs in the midst of several daily running jobs.  In other words, would you consider something like this a success, a failure, or a partial?   These factors need to be determined before you implement a daily backup status.  After going through these steps, you simply need to start programming, obtaining and aggregating data, and saving the results in order to produce accurate reports.

2.     Report on individual business units.
Most people that are reading this article are looking after a large amount of PCs, servers, and databases.  Many of these devices are simply names on a screen, and the challenge of valuing the data on each machine is very real.  In order to make these names more significant to you as the administrator, it is a good practice to pair the backup clients with the company configuration management database.  This way, you will be able to collect information such as business units or the application names in a much more memorable fashion.  You will also be able to begin reporting at the application or business unit level and thereby share the information with end users.  Bear in mind that there are many CMDB tools in existence, and the difficulty involved in extracting specific data programmatically can be significant.  In order to get this information, some people obtain an extra copy of the CMDB in a CSV file, and that way, the information is organized by columns that show the hostname, business unit name, and the application name.  With the availability of this information, administrators can then map it to the storage or backup status for each individual client.  As mentioned above, it can also be shared with end users, which is a huge benefit to all. 

3.     Report on your storage.
It is common desire for both managers and users alike to obtain knowledge about their storage usage.  Likewise, teams want this information in order to accurately forecast future storage needs and additional storage purchases.  Keeping a record of daily data points for all key elements is a good rule of thumb when reporting on your storage information.  In order to achieve this, you must look at the raw data, compress it, and then de-dupe it, if necessary.  Keep the level of granularity low, beginning with storage, and then moving on to storage pools, file systems, or shares, if applicable. Do remember that this data is only relevant for a few months after reporting.  You might also want to keep track of the deduplication ratio over time when considering the VTLs or other devices relating to deduplication.  The reason for this is because degradation will likely result in extra storage costs per TB of raw data, not to mention the additional processing cycles on the deduplication device.

4.     Don’t wait!  Be sure to automate!

You might be concerned that there will be loads of work you must do manually after reading this article.  Do not fear!  There are various solutions in the software world that will assist you in making many of the processes mentioned above automated.  The best part of this is that your system will be equipped to perform in a proactive manner, instead in one that is reactive.  By investing in appropriate software, you can be assured that your backup reporting strategy will be top-notch!

Amedee Potier joined Rocket Software in 2003 and is currently Senior Director of R&D, where he oversees several Rocket products in the Data Protection space. His focus is on solutions for data protection and management in heterogeneous multi-vendor and multi-platform environments.
Categories: DBA Blogs

With a Modern Storage Infrastructure, Companies Must Find an Excellent Data Management Tool

Tue, 2016-06-21 15:54
One of the more “weighty” questions within the IT world is in reference to the value of each company’s particular data.  Many wonder what the true value of protected data is in the long-run, eventually view it as a cost center where money continuously gets used up.  In order to make data work in the favor of a business and to help generate some income, companies must get smarter with their approaches to business and stop looking at their data this way!

The majority of companies out there admit to wanting a Big Data system as a part of their layout, but ultimately have nothing to show for these desires.  There have been many failed implementations despite lots of money spent on resources to help.  You might say that many businesses have the best intentions when comes to incorporating a data management plan, yet intention without action is arguably useless at the end of the day.  You might wonder why companies fail to follow through on their desires for a Big Data system.  Well, the answer is really quite simple.  The amount of data out there is staggering, and trying to manage it all would be like trying to boil the vast ocean.  You can imagine how difficult-if not impossible-that would be! 

Many question if the cloud would be a good solution, and if everyone should just get started on moving their data up there.  Or perhaps virtualization?  Would that be the answer?  These two tools are valuable, but the question stands on whether or not they are the best way to utilize company resources.  What companies really need is a tool that will aid in organizing data into appropriate categories.  In other words, this tool would be able to filter out what data should be implemented in order to create the most value for the company.

As stated above, the majority of corporations view their data as simply a cost center, aiding in the draining of company resources.  As human nature would have it, a lot of times the attitudes in reference to existing data reflects the heart of "out with the old, in with the new," and new projects that allow for more capacity or faster processing take precedence in time and funding.  It is as if the old data is a bothersome reality, always needing a little extra attention and focus, demanding greater capacity, and continuously needing backups and protection.  What companies forget to keep at the forefront of their minds however, is the fact that taking care of this old and “bothersome” data is a great way to clear up space on the primary storage, and who doesn’t appreciate that?  It also aids in generating a faster backup process (with a reduced backup size), which is again, a bonus for any company!  It should not be so quickly forgotten that this nagging and consistently needy data is essentially the ESSENCE of the company.  Would you believe that this “cost center” is also where it gains some of its income?  If companies kept these points in their minds, we would not be seeing such poor practices when it comes to data management.  One thing is clear however, and that is the point that initiative with managing data can be difficult, and many view the overwhelming ocean of growing data and the daunting task of trying to manage it as too great a calling to handle.
 
However, IT departments and administrators must bear in mind that there are tools out there to help them classify and organize data, which ultimately will be their proverbial life-boat when it comes time to accepting the challenge of managing data.  Look at it this way.  Let's say you are trying to find every single chemical engineer on earth.  Does that sound a bit overwhelming?  The question is, how would you go about doing this?  After all, there are over 7 billion people on this planet!  Where do you begin?  Do you profile EVERY person?  Of course not.  What you would likely do, in order to simplify this complex process, is organizing people into broad groups, maybe by profession.  After that, you would probably do something like research what particular people in that profession graduated with a degree in engineering.  Though basic, you can use these same principles when narrowing down data and trying to sort through the piles of information in a company.  One must use their intelligence and business knowledge to better grasp corporate data, and in return, this will help companies benefit from their data assets.  In order to do this, there are tools available to help administrators better comprehend and manage their data. 

These tools exist to give IT departments the upper hand in managing their spheres.  Can you imagine trying to manage large amounts of company data on your own?  Luckily, we don’t have to do such things, and we live in an age in which a variety of solutions are available to help companies not only survive, but thrive.  These tools are out there to empower IT teams to successfully weather the storms and changing tides of data types and volumes.  After using such tools, companies often experience improved storage capacity, less costs associated with backup and data protection -- and let’s not forget compliance!  Compliance is a hot topic, and with the help of appropriate data management solutions, companies will be guaranteed to meet the various regulatory compliance rules in today’s business world.

In closing, it is important to note that more networking tools will not do anything close to what the appropriate data management solutions can do.  Companies should be looking for solutions that can help them as well with tracking, classifying, and organizing file systems over their whole lifespan.  When the right tools get into the right hands, IT managers are better able to do their jobs! 


About the author: Jason Zhang is the product marketing person for Rocket Software's Backup, Storage, and Cloud solutions.

Categories: DBA Blogs

The Cost of Doing Nothing

Mon, 2016-06-13 12:18
For a business to become optimally successful, it absolutely must incorporate a quality life-cycle management system.  This begs the question:  Why do so many vendors miss the mark when it comes to providing the necessary updates and enhancements?  Developers and software companies should embrace their respective ALM systems as their staunch allies; and progressive IT organizations stay well ahead of the game by using progressive technology and best practices to ensure that high-quality products are on time and on budget while remaining fully compliant.  The goal of any ALM supplier should be to cater to its clients by properly supporting them by staying abreast of platform enhancements and being familiar with new languages, new devices, mobile demands, ever-changing compliance regulations and other real-time demands that must be continually addressed.

The bottom line remains:  in order for development leaders to not only survive, but thrive, they must make the transition to the most-updated ALM solution, possible.  Surprisingly, however, development leaders can be hesitant to utilize a modern ALM solution; but the cost of doing nothing can be more expensive than one might imagine. 

There are a handful of misguided reasons why an updated ALM solution might not be employed.  A few of those fallacies can include the following:

A New ALM Solution Would Be Too Cost-Prohibitive

Being the lead dog and staying ahead of the pack in this competitive world is absolutely paramount which is why a vendor must provide the crucial components such as product enhancements, platform updates, etc.  Research reveals some unsettling data:

  • 84% of IT projects become overdue or over budget
  • 31% of IT projects never reach completion due to being canceled
  • Completed IT projects deliver, on average, only 42% of their expected benefits

Accuracy and efficiency become the name of the game as it applies to profit; but developers' profit margins will be sorely compromised without up-to-date functionality and access to current tools via an up-to-date ALM system.  Additionally, if no automated system is integrated, IT will be forced to spend a good deal of valuable time addressing compliance-related issues; and that can be costly. 

Your vendor's R&D department should certainly be acutely aware of new trends in the industry as well as responsive to customer’s requests.  A coveted ALM solution will incorporate 1) on-board templates for compliance reporting 2) compatibility and remote access with any mobile device 3) tools such as dashboards, real-time reports & analytics and automated work-flows –  all, of which, enable every team-member to stay up-to-date. 

The cost of doing nothing can take a titanic toll when one considers that not meeting app-release time-lines as well as opportunities that become lost in the shuffle plus valuable time addressing compliance concerns and/or audits all cost a business, big-time!  The question, then, becomes obvious:  You believe you can't afford the integration of a modern ALM solution – but can you afford NOT to??

Our Current ALM Solution Seems to be Working Fine
In order to effectively, efficiently and optimally monitor, manage and coordinate all the people, processes, changes and dynamics that are intricately involved in the application life-cycle, utilizing the most sophisticated ALM solution is key!  Development personnel feel the demands of deploying functionality and fixes, very quickly.  The IT setting is extremely complex; and in this environment, database servers, web servers, diverse clientele and every type of mobile device equate to sophisticated development and release processes.  All this must be intricately orchestrated without a hitch; and a modern ALM solution is what it takes to fully ensure a flawless and seamless operations in every department. 

With the most modern ALM solution, users can enjoy the ease at which systems and work-flows come together in addition to the minimization of production errors and the maximization of collaboration efforts.  Then, imagine all this coupled with data access from any mobile device, compliance reports with point-and-click ease and automation processes that are as easy as child's play.

Older ALM solutions are just that 'old' and with that, comes the inability for an archaic solution tool to offer the newest of technologies which equates to lost time due to fixing bad code and dealing with coding errors, as only a single example.  And then, of course, there is the lost revenue.  In the end, the growth of a company is stifled.  Again, a modern ALM solution keeps a business in position as the 'alpha' and leader of the competitive pack since the people and processes involved are all humming like a fine-tuned engine – no, barricades, no inefficiency and virtually no errors.

Transitioning to a New ALM Would Be Too Time-Consuming

How one chooses a vendor can make the difference between reaping the benefits of a dedicated and seasoned professional with an unparalleled product that he or she is excited to share, verses a vendor whose interest in your goals and progress is marginal, at best.  Assuming the right vendor has been selected, the time required to get the system fully running will be miniscule.  Personnel can very quickly enjoy immediate visibility, coordination and management across distributed systems, teams and tools.  In the end, previously-lost revenue due to outdated ALM systems becomes a distant memory since teams will no longer contend with drawn-out, manual processes but will, now, have the updated abilities to very quickly communicate, collaborate, update etc. regarding any and all application projects. 

Not one single team-member needs to concern him or herself with transitioning into an updated system.  A committed vendor will make sure the necessary and expected support is entirely available for everyone involved.  Again, in the end, any time invested in becoming familiar with a new ALM solution will begin to immediately pay for itself due to optimized usability involving real-time visibility, flexibility, accuracy and automation.

Our Current ALM Serves Only Development

When a business chooses stagnation over progress, it can become the 'kiss of death' for the organization.  Because technology will never slow down or even reach an apex, a business absolutely must stay on track with innovative ideas, processes and insights.  An integrated ALM system ensures that users can take full advantage of managing, in real-time, every aspect of development and delivery.  A top-tier ALM solution will provide instantaneous updates on every component ranging from code to work-flow to dashboards and everything in-between and beyond.  Smarter, more-insightful decisions become common-place among everyone involved – whether development personnel, auditors, programmers, etc.  Since DevOps departments evolve and advance in the enterprise, so too, must the ALM system by functioning as the centralized collaborative arena where inter-department communications are available whenever and wherever required.

After it's all said and done, switching to a modern ALM solution will, realistically, save money over the long haul since time is being dramatically saved – and time is money!  Those few words serve as a cliché as well as a fact.  Whether one is speaking of departments collaborating on changes at any level, or enhanced visibility that maximizes work-flow or whether one is talking about users gaining advanced capabilities resulting in succinct, precise and quick decision-making, it all adds up, once again, to saving copious amounts of time which translates into saving impressive amounts of revenue.

A reliable vendor will provide the kind of support one would expect from a supplier that operates as a top-tier contender in the industry.  Vendor support should include:

  • Access to the most up-dated interfaces and devices
  • Assistance with any existing OS
  • Intervention for all platforms, on which, code is being developed
  • Mobile and web development
  • Out-of-the-box plug-ins to converge with other tools
  • Compliance-report templates
  • Delivery of single-screen visibility with all IT involvement
  • Adjustable point-and-click distribution and deployment and mobile functionality with everything

It is an ever-changing business climate where technology is king.  And...

                                            Adaptation equals growth and growth equals SUCCESS!    


About the author: Daniel Magid is Rocket’s IBM i solution leader and Director of the Rocket Application Lifecycle Management (ALM) and DevOps lab. Having started his career at IBM in 1981 in the midrange computer division, Daniel brings to Rocket Software more than 30 years of experience in the IBM midrange marketplace. Prior to coming to Rocket as part of the acquisition of Aldon in 2011, Daniel was Aldon’s CEO and Chief Product Strategist. Daniel led the growth of Aldon from a small 4 person consulting company to the largest provider of ALM and DevOps solutions in the IBM i market. Daniel is a recognized expert in application development and DevOps in the IBM i market and a well-known presence at IBM i conferences.




Categories: DBA Blogs

Database Migration and Integration using AWS DMS

Thu, 2016-05-12 14:12


Amazon Web Services (AWS) recently released a product called AWS Data Migration Services (DMS) to migrate data between databases.

The experiment

I have used AWS DMS to try a migration from a source MySQL database to a target MySQL database, a homogeneous database migration.

The DMS service lets you use a resource in the middle Replication Instance - an automatically created EC2 instance - plus source and target Endpoints. Then you move data from the source database to the target database. Simple as that. DMS is also capable of doing heterogeneous database migrations like from MySQL to Oracle and even synchronous integrations. In addition AWS DMS also gives you a client tool called AWS Schema Converter tool which helps you convert your source database objects like stored procedures to the target database format. All things a cloud data integration project needs!

In my experiment and POC, I was particularly interested in the ability of the tool to move a simple data model as below, with 1-n relationship between tables t0(parent) and t1(child) like below.

(Pseudo code to quickly create two tables t0, t1 with 1-n relationship to try it. Create the tables both on source and target database)

t0 -> t1 Table DDL (Pseudo code)

CREATE TABLE `t0` (
  `id` int(11) NOT NULL,
  `txt` varchar(100) CHARACTER SET ucs2 DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `t1` (
  `id` mediumint(9) NOT NULL AUTO_INCREMENT,
  `t0id` int(9) DEFAULT NULL,
  `txt` char(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `t0id` (`t0id`),
  CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`t0id`) REFERENCES `t0` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


In this experiment, I didn't want to see just a migration, a copy, of a table from source database to a target database. I was interested more to see how easy is to migrate a data model - with Primary Key and Foreign Key relationship in place -  from the source database to the target database with zero downtime and using their CDC (Changed data Capture) or Ongoing-Replication migration option and capabilities of AWS DMS. That is, zero downtime database migration.

Here are the results of the experiment.

AWS DMS is ubiquitous, you can quickly set-up an agent (Replication Instance) and define source & target endpoints and start mapping your tables to be migrated from source database to target database with the tool. All conveniently using the AWS console.

Once you setup your replication instance and endpoints, create a Migration Task (say Alpha) and do an initial full migration (load) from the source database to the target database. Do this with the foreign keys (FKs) disabled on the target. This is a recommendation in the AWS DMS Guide in order to dump the data super fast as it does it with parallel threads, at least this is the recommendations for MySQL targets.

Then you can create a second Migration Task (say Beta) using a different endpoint, but this time with the foreign keys enabled on the target. You can do this even before your full load with Alpha to avoid waiting times. Configure Beta interface/task to run forever and let it integrate and sync the delta which occurred during the initial load. You can even start the Beta interface from a cut-off timestamp point. It uses source MySQL database's binlogs to propagate the changes. If you don't create beta interface, that is to use a different endpoint for the target with the parameter which enables the FKs, the DELETE SQL statements on the source which occur during the migration will not propagate to the target correctly and the CASCADEs to the child tables will not work on the target. CASCADE is a property of the Foreign Key.

To reconcile, to find out if you have migrated everything, I had to count the rows in each table on source and the target databases to monitor and see if it all worked. To do that I used Pentaho Spoon CE to quickly create a job to count the rows on both source and target database and validate migration/integration interfaces.

Overall, I found AWS DMS very easy to use, it quickly helps you wire an integration interface in the Cloud and start pumping and syncing data between sources and targets databases be it on Premise or Cloud. A kind of Middleware setup in AWS style, in the Cloud. No more middleware tools for data migration, AWS now has it's own. 
Categories: DBA Blogs

LEVERAGE GEOGRAPHICALLY-DISTRIBUTED DEVELOPMENT

Wed, 2015-12-23 14:44
As technology advances at warp speed, there are certain tech methodologies that will go by the wayside to make room for more advanced and efficient versions; and how development projects are managed is a case, in point.  Companies in every industrialized nation of the world are embracing Geographically-Distributed Development or GDD, which has embedded itself an impressive and proof-positive IT strategy model.  Outdated procedures that have been utilized for the administration of virtually any type of development project have been limited to one or several building sites.  That was then; this is now.

Let’s take a look at the advantages that GDD offers:
decreased labor expenses
increased availability to skilled resources
reduced time-to-market, with round-the-clock flexible staffing
The beauty of GDD is that is allows enterprises, regardless of location, to respond to changes in business circumstances as they happen.  Any feedback can be presented, instantaneously, within a global framework.

In order for GDD to achieve its vast benefit potential, major barriers that might impede an enterprise’s successes must be reduced to a minimum or entirely eliminated within the GDD strategy.   It is crucial that increased expenses associated with communication and coordination logistics that occur on an international level within a globally-distributed market, be uncovered and targeted.  If communication and coordination-specific expenses are allowed to flourish, the very benefits of GDD can be sorely compromised.  Various challenges must be reckoned with:  1) cultural variances 2) language differences and 3) inaccessibility to time-sensitive information.  These can all jeopardize the progress of distributed projects.

GDD is oblivious to location. it is an IT strategy model without borders.  This allows development team-members to work collectively and cohesively within a city, across state lines or beyond continents.  A site or sites might be engaged with one particular software-development venture while one or more outsourcing companies work, simultaneously, towards the projected goal.   Outsourcing companies would contribute their efforts and expertise, like a fine-tuned engine, within the software’s project-development cycle.  Optimized efficiency and cost savings, via structured and coordinated local or global team-work, becomes refreshingly realized.

With GDD, thorough and clear communication is established between all team members and project coordination.  Business demands incorporate global-sourcing, service-oriented architecture, new compliance regulations, new development methodologies, reduced release cycles and broadened application lifetimes.  Because of this, highly-effective, unencumbered communication is mission-critical; and a necessity arises that begs for a solution that has the power to:
Provide management visibility of all change activities among distributed development teams 
Integrate and automate current change processes and best practices within the enterprise
Organize the distribution of dependent change components among platforms and teams
Protect intellectual property

Track and authenticate Service Level Agreements (SLAs)
Engaging an organization to efficiently manage and significantly optimize communication among all stakeholders in the change process is a priceless component of an Application Lifecycle Management (ALM) solution.  Multiple GDD locales present inherent challenges:  language and cultural divides, varying software-development methods, change-management protocol, security employment, adherence to industry mandates and client business requisites.  The good news is the ALM solution tackles these hurdles with ease!

Provide Management Visibility of all Change Activities among Distributed Development Teams

When a centralized repository allows for the viewing of all the activities, communications and artifacts that could be impacted by the change process, you have beauty in motion; and this is what ALM does.  Via ALM, users have the luxury of effortlessly viewing project endeavors by each developer, development group or project team--irrespective of location, platform and development setting.  This type of amenity becomes especially striking when one begins to compare this model-type with other distributed environments where work-in-progress is not visible across teams due to niche teams employing their own code repositories.

ALM provides the opportunity for development managers to not only track, but validate a project’s standing.  A project’s status can be verified which helps to guarantee the completion of tasks.  User-friendly dashboards will alert management if vital processes indicate signs of sluggishness or inefficiency.

ALM ensures that the overall development objectives will be met on a consistent basis.  This is accomplished through the seamless coordination between both remote and local development activities.  The ALM-accumulated data plays a crucial role with boosting project management, status tracking, traceability, and resource distribution.  Development procedures can be continually improved upon, thanks to generated reports that allow for process metrics to be collected and assessed.  Also, ALM allows regulatory and best-practices compliance to be effortlessly monitored and evaluated.  Compliance deals with structuring the applicable processes and creating the necessary reports.  ALM executes compliance strategy and offers visibility to the needed historical information, regardless of users’ geographic locations.

Integrate and Automate Current Change Processes and Best Practices within the Enterprise

In a perfect world, each and every facet of a company’s application development would be super easy; and with ALM it is.  By way of ALM, companies can establish the defined, repeatable, measureable and traceable processes based on best practices, with absolute perfection.  User-friendly point-and-click set-up functions enable one to create a collection of authorized processes that automate task assignments and movement of application artifacts.

ALM permits the streamlining of change management by means of its simplicity when dealing with changes and necessary proceedings.  This in turn means changes can be analyzed and prioritized.  The approval management functions demand that official authorizations must be secured before any changes are permitted to go forth.  The ATM’s automated logging functions totally un-complicate the tracking of software changes.  This is huge since changes can be tracked from the time a request is received up to the time when a solution is submitted to production.

Every member that is part of the global development team would be duly notified regarding any required assignments as well as any happenings that would have an impact on their efforts.

Organize the Distribution of Dependent Change Components among Teams and Platforms

It’s no secret that when there are changes within just one system of a cohesive enterprise, those changes can impact other systems.  ALM offers multi-platform support which ensures that modifications made on disparate platforms, by way of geographically-dispersed teams can be navigated through the application lifecycle jointly.  A Bill of Materials Process, or BOMP, serves as an on-board feature that permits users to create file portfolios that incorporate characteristics from various platforms.  This means those portfolios can travel through the lifecycle as a unit.  Additionally, some ALM solutions absolutely ensure that the parts within the assemblies are positioned with the suitable platforms at each state of the lifecycle. 

Protect Intellectual Property

An ALM solution is the perfect component that allows for access and function control over all managed artifacts.  Managers are in a position to easily determine and authorize any access to vital intellectual property due to ALM functioning on a role-based control system.  The role-based structure means administrative operations are streamlined which permits any system administrator to bypass assigning individual rights to a single user.  Additionally, a role-based system delivers a clearly-defined synopsis of access rights between groups of individuals.

Track and Authenticate Service Level Agreements

The overall project plan, absolutely, must remain on schedule while administering accountability for established deliveries; and this can be easily realized through ALM’s ability to track and authenticate tasks and processes.  The ALM solution caters to satisfying Service Level Agreement (SLA) requirements within an outsourcing contract.  As a result, project management is enhanced by ensuring performance of specific tasks.  Optimizing the user’s ability to track emphasized achievements is made possible due to the consistency between tasks that have been assigned to developers and tasks that are part of the project plan.   Invaluable ALM-generated reports will track response and resolution times; and service-level workflows automate service processes and offer flexibility.  This translates into an acceleration of processes to the respective resources to meet project deadlines.  The ability to track performance against service-level agreements is made possible due to the availability of reports and dashboards that are at one’s fingertips.

Enhance Your Geographically-Distributed Development

As stated, ALM is beauty in motion; and aside from promoting perfected-levels of communication and coordination, it utilizes management strategies designed to plow through any obstructions that have the potential to compromise success.   ALM’s centralized repository is purposed to present multiple ideas, designs, dialogue, requirements, tasks and much more to team-members who would require or desire instant access to data.  Development procedures and tasks can be precisely and efficiently automated and managed due to ALM’s cohesive workflow capabilities.  Vital intellectual property, all of it is embedded and safeguarded in a central repository.  Due to this caliber of reinforced protection, loss and unauthorized access is null and void.  When remote software development is in-sync with local development, project management becomes seamless, highly-coordinated and error-free.  Integration of the monitoring, tracking and auditing of reports and dashboards means management can successfully satisfy project deadlines.  It would behoove any enterprise who wishes to reap the rewards of GDD to fully embrace ALM as its solution, it is truly mission-critical.

Application Lifecycle Management Solutions

Application lifecycle management programs are able to easily deliver a plethora of enterprise software modifications and configuration management facilities.  ALM solutions have the ability to support the needs of multiple groups of geographically-distributed developers.  Business process automation services, designated to automate and enforce on-going service delivery processes throughout enterprise organizations, is a vital component of ALM solutions.  Within  those groups of geographically-distributed developers, the product continues to reveal the magnitude of its talents since it:   targets permission-based assignment and enforcement services, caters to role-based interfaces which allows support for developers, software engineers, project managers, IT specialists, etc, delivers enterprise application inventory-management services, oversees and coordinates  large software inventories and configurations, guards user access, manages differing versions of application code,  supports the existence of concurrent development projects, coordinates a diversity of release management facilities. 

Mike Miranda is a writer concerning topics ranging from Legacy modernization to Application life cycle management, data management, big data and more



Categories: DBA Blogs

Oracle Apex 5.0 and APEX_JSON

Sat, 2015-12-12 04:09
How many lines of code does it take to make a web service call? Answer: 39

That is how many lines of PL/SQL I had to write in Oracle Apex 5.0 to make a web service call to an external API.

I used Adzuna's REST API to retrieve the latitude and longitude and the price of 2 bed properties for rent in a specific location in UK. The API returns JSON which the APEX_JSON package is able to parse easily. Adzuna is a property search engine which also provides aggregate data for properties in various countries around the world.

I think the native APEX_JSON package in Oracle Apex 5.0 is very useful and makes integrating web services to your Oracle Apex applications very easy. Here is application I have created in matter of hours which shows you average rent properties in a location of your choice in UK.

Here is the link for the app:  http://enciva-uk15.com/ords/f?p=174:1













And here is the code:


If you want to run this as is in your SQL Workshop, make sure you replace {myadzunaid:myadzunakey} in the code with your adzuna app_id and app_key which you can obtain from the Adzuna website https://developer.adzuna.com/ as I have removed mine from the code. They also have a very good interactive api documentation here http://api.adzuna.com/static/swagger-ui/index.html#!/adzuna


create or replace procedure get_rent_data(p_where in varchar2, p_radius in number, p_room in number)
is
v_resp_r clob;
j apex_json.t_values;
l_paths apex_t_varchar2;
v_id varchar(50);
v_lat decimal(9,6);
v_lon decimal(9,6);
v_rent number(10);

begin
-- http housekeeping
apex_web_service.g_request_headers(1).name  := 'Accept'; 
apex_web_service.g_request_headers(1).value := 'application/json; charset=utf-8'; 
apex_web_service.g_request_headers(2).name  := 'Content-Type'; 
apex_web_service.g_request_headers(2).value := 'application/json; charset=utf-8';

v_resp_r := apex_web_service.make_rest_request 
      ( p_url => 'http://api.adzuna.com:80/v1/api/property/gb/search/1' 
      , p_http_method => 'GET' 
      , p_parm_name => apex_util.string_to_table('app_id:app_key:where:max_days_old:sort_by:category:distance:results_per_page:beds') 
      , p_parm_value => apex_util.string_to_table('{myadzunaid:myadzunakey}:'||p_where||':90:date:to-rent:'||p_radius||':100:'||p_room||'') 
      );
-- parse json
apex_json.parse(j, v_resp_r);


-- start looping on json
l_paths := apex_json.find_paths_like (
        p_values         => j,
        p_return_path => 'results[%]',
        p_subpath       => '.beds',
        p_value           => '2' );
        
for i in 1 .. l_paths.count loop
       v_id := apex_json.get_varchar2(p_values => j, p_path => l_paths(i)||'.id'); 
       v_rent := apex_json.get_varchar2(p_values => j, p_path => l_paths(i)||'.price_per_month'); 
       v_lat := apex_json.get_varchar2(p_values => j, p_path => l_paths(i)||'.latitude');
       v_lon := apex_json.get_varchar2(p_values => j, p_path => l_paths(i)||'.longitude');

-- debug print values to page
 htp.p(v_id||'-'||v_lat||','||v_lon||'Rents : £'||v_rent);

end loop;

END;

Categories: DBA Blogs

Five Questions to Ask Before Purchasing a Data Discovery Tool

Thu, 2015-12-10 17:14
Regardless of what type of industry or business you are involved in, the bottom-line goal is to optimize sales; and that involves replacing any archaic tech processes with cutting-edge technology and substituting any existing chaos with results-driven clarity.
Data discovery tools, being a business-intelligence architecture, creates that clarity through the incorporation of a user-driven process that searches for patterns or specific items in a data set via interactive reports.  Visualization is a huge component of data discovery tools.  One can merge data from multiple sources into a single data set from which one can create interactive, stunning dashboards, reports and analyses.  The user is able to observe data come to life via striking visualizations.  Furthermore, business users want to perform their own data analysis and reporting—with a data discovery tool they can!  After it’s all said and done, smarter business decisions are generated; and that drives results.
Before purchasing a data discovery tool, however, several questions should be addressed:

1: What About Data Prep?

It’s important to realize that there are companies who will claim their data-discovery products are self-service; but keep in mind that many of the products will necessitate a data prep tool in order to access the data to be analyzed.  Preparing data is challenging; and if a data prep tool is not included, one must be purchased.  Choose a data discovery tool that enables data prep to be handled without any external support.
As a side note:  governed self-service discovery provides easy access to data from IT; and an enterprise discovery platform will give IT full visibility to the data and analytic applications while it meets the business’s need for self-service.  Business users embrace the independence they are empowered with to upload and combine data on their own.  

2:  Is Assistance from IT Required for Data Discovery Tools?

Business users desire the ability to prepare their own, personal dashboards and explore data in new ways without needing to heavily rely on IT.  Data discovery tools do not require the intervention of IT professionals, yet the relationship with IT remains.  Data discovery tools empower the business to self-serve while maintaining IT stewardship.  Data discovery tools allow users to directly access data and create dashboards that are contextual to their needs—whenthey need it and how they need it!  This, in turn, reduces the number of requests for reports and dashboards from IT staff and allows those professionals to focus more intently on development projects and system improvements.  Software solutions that support data discovery, such as business intelligence platforms with innovative visualization capabilities, are zealously supported by non-technical business users since they can perform deep, intuitive analysis of any enterprise information without reliance on IT assistance.  
      
3:  Will Data Discovery Tools Allow One to Interact with the Data?

The fun thing is, you can play with the data to the point of being able to create, modify and drill down on a specific display.  A beautiful feature of data discovery tools is the interactive component which permits one to interact with corporate data sources visually to uncover hidden trends and outliers.  Data discovery facilitates intuitive, visual-based and multivariate analysis via selecting, zooming, pivoting, and re-sorting to alter visuals for measurement, comparisons and observation.

4:  Are Data Discovery Tools Intended for Enterprise Use?

Enabling the business to self-serve while maintaining IT stewardship creates reliable decisions the enterprise can rely on.  Data discovery tools are invaluable for enterprise use—organizations can plan their approach to incorporate data discovery tools into their infrastructure and business practice. 
Data discovery tools allow one to retrieve and decipher data from spreadsheets, departmental databases, enterprise data warehouse and third-party data sources more efficiently than ever!  Multidimensional information can be transformed into striking graphical representations—3D bar and pie charts, histograms, scatter plots and so much more!  Data discovery tools deliver enterprise solutions within the realms of business information and analytics, storage, networks & compliance, application development, integration, modernization and database servers and tools.  

5:  With Data Discovery Tools Can I Retrieve Answers At any Time?

Data discovery tools will allow you to make inquiries and get answers quickly and seamlessly.  Geographic location will make no difference since files can be loaded on a laptop or even a mobile phone or other mobile devices.  With a few clicks, you can unlock all your data from servers, a mainframe or a PC. 
Multiple charts, graphs, maps and other visuals can, all, be combined in analytic dashboards and interactive apps.  Answers to crucial questions and issues can be quickly unveiled.  Analysts can share the data, with ease, among all users via the web and mobile devices—all operating like a fine-tuned engine—anytime, anywhere.       

Data discovery tools are changing business intelligence!

Mike Miranda writes about enterprise software and covers products offered by software companies like rocket software about topics such as Terminal Emulation, Legacy Modernization, Enterprise Search, Big Data, Enterprise Mobility and more.
Categories: DBA Blogs

Why Data Virtualization Is so Vital

Tue, 2015-11-24 16:35
In today’s day and age, it probably seems like every week you hear about a new form of software you absolutely have to have. However, as you’re about to find out, data virtualization is actually worth the hype.

The Old Ways of Doing Things

Traditionally, data management has been a cumbersome process, to say the least. Usually, it means data replication, data management or using intermediary connectors and servers to pull off point-to-point integration. Of course, in some situations, it’s a combination of the three.

Like we just said, though, these methods were never really ideal. Instead, they were just the only options given the complete lack of alternatives available. That’s the main reason you’re seeing these methods less and less. The moment something better came along, companies jumped all over them.
However, their diminishing utility can also be traced to three main factors. These would be:

  • ·      High costs related to data movement
  • ·      The astronomical growth in data (also referred to as Big  Data)
  • ·      Customers that expect real-time information
These three elements are probably fairly self-explanatory, but that last one is especially interesting to elaborate on. Customers these days really don’t understand why they can’t get all the information they want exactly when they want it. How could they possibly make sense of that when they can go online and get their hands on practically any data they could ever want thanks to the likes of Google? If you’re trying to explain to them that your company can’t do this, they’re most likely going to have a hard time believing you. Worse, they may believe you, but assume that this is a problem relative to your company and that some competitor won’t have this issue.

Introducing Data Virtualization

It was only a matter of time before this problem was eventually addressed. Obviously, when so many companies are struggling with this kind of challenge, there’s quite the incentive for another one to solve it.

That’s where data virtualization comes into play. Companies that are struggling with having critical information spread out across their entire enterprise in all kinds of formats and locations never have to worry about the hardships of having to get their hands on it. Instead, they can use virtualization platforms to search out what they need.

Flexible Searches for Better Results

It wouldn’t make much sense for this type of software to not have a certain amount of agility built in. After all, that’s sort of its main selling point. The whole reason companies invest in it is because it doesn’t get held back by issues with layout or formatting. Whatever you need, it can find.

Still, for best results, many now offer a single interface that can be used to separate and extract aggregates of data in all kinds of ways. The end result is a flexible search which can be leverage toward all kinds of ends. It’s no longer about being able to find any type of information you need, but finding it in the most efficient and productive way possible.

Keep Your Mainframe

One misconception that some companies have about data virtualization is that it will need certain adjustments to be made to your mainframe before it can truly be effective. This makes sense because, for many platforms, this is definitely the case. These are earlier versions, though, and some that just aren’t of the highest quality.

With really good versions, though, you can basically transform your company’s mainframe into a virtualization platform. Such an approach isn’t just cost-effective. It also makes sure you aren’t wasting resources, including time, addressing the shortcomings of your current mainframe, something no company wants to do.

Don’t get turned off from taking a virtualization approach to your cache of data because you’re imagining a long list of chores that will be necessary for transforming your mainframe. Instead, just be sure you invest in a high-end version that will actually transform your current version into something much better.

A Better Approach to Your Current Mainframe

Let’s look at some further benefits that come from taking this approach. First, if the program you choose comes with the use of a high-performance server, you’ll immediately eliminate the redundancy of integrating from point-to-point. This will definitely give you better performance in terms of manageability. Plus, if you ever want to scale up, this will make it much easier to do so.

Proper data migration is key to a good virtualization process. If it is done right the end user wont have to worry out corrupted data and communication between machines will be crystal clear.
If you divert the data mapping you need to do at processing-intensive level and transformation processes away from the General Purpose Processor of your mainframe to the zIIP specialty engine, you’ll get to dramatically reduce your MIPS capacity usage and, therefore, also reduce your company’s TCO (Total Cost of Ownership).

Lastly, maybe you’d like to exploit of every last piece of value you derive from your mainframe data. If so, good virtualization software will not only make this possible, but do so in a way that will let you dramatically turn all of your non-relational mainframe data virtualization into relational formats that any business analytics or intelligence application can use.

Key Features to Look for in Your Virtualization Platform

If you’re now sold on the idea of investing in a virtualization platform, the next step is getting smart about what to look for. As you can imagine, you won’t have trouble finding a program to buy, but you want to make sure it’s actually going to be worth every penny.

The first would be, simply, the amount of data providers available. You want to be able to address everything from big data to machine data to syslogs, distributed and mainframe. Obviously, this will depend a lot on your current needs, but think about the future too.

Then, there’s the same to think about in terms of data consumers. We’re talking about the cloud, analytics, business intelligence and, of course, the web. Making sure you will be able to stay current for some time is very important. Technology changes quickly and the better your virtualization process is the longer you’ll have before having to upgrade. Look closely at the migration process, and whether or not the provider can utilize your IT team to increase work flow. This will help you company get back on track more quickly and with better results.

Finally, don’t forget to look at costs, especially where scalability is concerned. If you have plans of getting bigger in the future, you don’t want it to take a burdensome investment to do so.
As you can see, virtualization platforms definitely live up to the hype.You just have to be sure you spend your money on the right kind.

Mike Miranda writes about enterprise software and covers products offered by software companies like Rocket software about topics such as Terminal emulation,  Enterprise Mobility and more.
Categories: DBA Blogs

How Terminal Emulation Assists Easy Data Management

Wed, 2015-11-18 21:25
Just about every company will need terminal emulation at some point. Yours may not need it now, but as time goes on, the day will come when you need information that can only be accessed with an emulator. This software allows a computer to basically take on the functionality of an older version. Doing so makes it possible for the user to access data that would otherwise be impossible to find. If you’re not sold on the benefits of using this type of software, consider the following ways that it assists with making data management easier.

Obtain Data from Outdated Systems

This is the most obvious way a terminal emulator helps with data management. Right now, you could have all kinds of data out of reach because it was created with software you no longer used or stored on a platform that differs from the current one on your system.

Without an emulator, you’re basically without options. You simply have to find a workaround. The best solution would involve using a machine with the old operating system installed. This isn’t just extremely inconvenient though; it also isn’t very cost-effective and is sure to become a bottleneck sooner or later.

With terminal emulation, no data ever has to be out of reach. Whether its information from 10 years ago or 20, you can quickly retrieve whatever it is you need.

Access Multiple Terminals at Once

There’s far more these applications can do to assist with data management though. Over time, your company has probably—or will, someday—go through multiple platforms. This means that going back and grabbing the data you need could involve more than one system. If you tried using the aforementioned workaround, you’d be in for a huge challenge. It would take multiple computers, each with a specific operating system and then going between them to get the information you need or cross reference it as necessary.
Modern emulators can access as many terminals as you need all on the same computer. Usually, it just involves putting each one on separate tabs. Not only can you get all the info you need, then, you can do it from the screen all at once. This makes it extremely easy to cross reference data from one system against others.

Customize Your Queries
Another great benefit that comes with terminal emulation is that you can actually customize your searches to various degrees. For many companies, accessing old data means looking at screens that represent the info in the most rudimentary of ways. There may only be a few colors used for fonts on a black screen. Obviously, this can make data management a bit of a challenge, to say the least.
With the right software, though, you can control the font color, the character size, background and more. This makes it much easier to see the information you want, highlight it and otherwise keep track of the data. Never again suffer through old screens from outdated platforms when you need something.

Mobile Functionality

Like just about everything these days, terminal emulators have now gone mobile. You can now pull off emulation from just about anywhere in the world on your mobile device. This is a great way to make emulation possible for any work at home employees who may be working for your company. If you hire on a consultant or freelance professional from somewhere outside the office, mobile functionality means they can now benefit from easy data management. Next time you’re on a business trip and need to access information from yesteryear, the ability to do so will be at your fingertips.

Mobile functionality may not seem like the most important aspect to have as far as emulation is concerned, but it’s better to have the option there than wish it was possible.

Save Money

Data management can be a costly endeavor. Unfortunately, it’s one of those costs your company really can’t hope to avoid. You need to manage your data, so you better find room in your budget. With terminal emulation, you can at least save money on this essential process.

Like we brought up earlier, without an emulator, you’re looking at a lot of hardware in order to make sure vital data is never out of reach, to say nothing of how inconvenient that option would be. You’re also looking at the potential costs of losing that data if anything happens to your dedicated machines. Furthermore, added hardware always comes with extra costs. There’s the space you need, electricity, IT support and more.

In light of those added expenses, simply using emulation software should be a no-brainer. You don’t need any extra hardware and these platforms are designed to stay out of the way until you need them, so they won’t hurt your staff’s ability to use their current machines.

Limitless Scalability

No matter what kind of software we’re talking about, scalability always needs to be a concern. Today, your company may only have so much data to manage. A year from now, though, there’s going to be a lot more. Five years from now, your company’s collection of data will be expansive.
Obviously, managing this data efficiently is going to take a resource that can scale without difficulty. Keep in mind that a lot of companies increase their amount of data exponentially. This means scalability is necessary, but so is being able to scale at a moment’s notice to whatever size is needed.
Terminal emulation and scalability are virtually inseparable when you have the right software. No alternative solution is going to be able to keep up. Again, if you tried using hardware to manage your data, you can forget about easy scalability and forget about doing so without spending a lot of money in the process.


Data management needs to be a priority for every organization, no matter what industry they’re in. However, simple data management isn’t enough anymore. Instead, you need emulation software that will make it easy, cost-effective and scalable. Otherwise, your business will always be greatly limited in what information it can access and the benefits that could be derived from it.


Mike Miranda writes about enterprise software and covers products offered by software companies like Rocket software about topics such as Terminal emulation,  Enterprise Mobility and more.
Categories: DBA Blogs

The Importance of Data Virtualization

Fri, 2015-09-04 12:37
It’s been a long time since the way data was stored and accessed has been addressed. We went from scrolls to books to mainframes and this last method hasn’t budged all that much over the last decade or so. This is despite the fact that we keep creating more and more information, which means that better ways for storing and finding it would make a world of difference. Fortunately, this is where data virtualization comes into play. If your company isn’t currently using this type of software, you’re missing out on a better way of leveraging your organization’s data.

Problems with Traditional Methods

No matter what line of work you’re in, your company is creating all kinds of information each and every business day. We’re not just talking about copy for your website or advertising materials either. Information is created with each and every transaction and just about any time you interact with a customer.

You’re also not just creating information in these moments. You need to access stored data too. If you don’t do this well—and many, many companies don’t—you’re going to end up with a lot of unnecessary problems. Of course, you’re also wasting a lot of money on all that info you’re creating but not using.

The main problem with traditional methods of data storage and retrieval is that they rely on movement and replication processes and intermediary servers and connectors for integrating via a point-to-point system. This worked for a long time. For a few companies, it may seem like it’s still working to some degree.

However, there’s a high cost to this kind of data movement process. If you’re still trying to shoulder it, chances are your overhead looks a lot worse than competitors that have moved on.
That’s not the only problem worth addressing, though. There’s also the huge growth of data that’s continuing to head north. We’ve touched on this, but the problem is so prevalent that there’s a term for it throughout every industry: Big Data. Obviously, it refers to the fact that there is just so much information out there, but the fact this term exists also shows how much it affects all kinds of companies.

Big Data is also a statement about its creation. Its sheer proliferation is massive. Every year, the amount increases at an exponential rate. There’s just no way the old methods for storing and finding it will work.

Finally, customers expect that you’ll be able to find whatever information you need to meet their demands. Whether it’s actual information they want from you or it’s just information you need to carry out their transaction, most won’t understand why this isn’t possible. After all, they use Google and other search engines every day. If those platforms can find just about anything for free, why can’t your company do the same?

The Solution Is Data Virtualization

If all of the above sounded a bit grim, don’t worry. There’s a very simple solution waiting for you in data virtualization. This type of software overcomes the challenges that come with your data being stored all over your enterprise. You never again have to run a million different searches to collect it all or come up with some halfway decent workaround. Finally, there’s a type of platform made specifically for your company’s data gathering needs.
This software isn’t just effective. It’s convenient too. You work through one, single interface and can have access to the entirety of your company’s data store. What it does is effectively separate the external interface from the implementation going on inside.

How It Works

Every data virtualization platform is going to have its own way of working, so it’s definitely worth researching this before putting your money behind any piece of software. However, the basic idea remains the same across most titles.
With virtualization software, the data doesn’t have to be physically moved because this technology uses meta data to create a virtual perspective of the sources you need to get to. Doing so provides a faster and much more agile way of getting to and combining data from all the different sources available:
·      Distributed
·      Mainframe
·      Cloud
·      Big Data
As you can probably tell, this makes it much easier to get your hands on important information than the way you’re currently doing it.

Finding the Right Title for Your Company

Although they all serve the same purpose and the vast majority will follow the outline we just went through, there are still enough virtualization software titles out there that it’s worth thinking about what your best option will look like. As with any type of software, you don’t want to invest your money anywhere it’s not going to do the most good.
The good news is that this software has been around long enough that there have been best practices established for how it should work.
First, you want to look for a title that will actually transform the way your mainframe works by leveraging what it can do for every search. This is just a good use of your resources and gives you more bang for your buck as far as your mainframe is concerned. Software that uses it for virtualization purposes is going to work even better than a distribution-based application and won’t cost any more.

However, it’s also going to work a lot better for that price too. A lot of companies also love that this way of carrying out a search is more secure as well. The last thing you want is to pay for a piece of software that’s actually going to leave you worse off.

Secondly, although this may sound complex, you can and should find a solution that keeps things simple. Data integration can be straightforward with the method we just described without any need for redundant processes that would slow down your ability to scale up.

With data virtualization, there is no downside. Furthermore, it’s becoming more and more of a necessity. Companies are going to have to invest in this software as Big Data continues to get bigger.



Mike Miranda writes about enterprise software and covers products offered by software companies like Rocket Software.about topics such as Terminal Emulation, Legacy Modernization, Enterprise Search, Big Data and Enterprise Mobility.


Categories: DBA Blogs

Don’t Settle When It Comes to Enterprise Search Platforms

Fri, 2015-09-04 12:32
No company should try to operate for very long without an enterprise search platform. With so many options out there, though, you could be confused about which one is worth a spot in your organization’s budget. Let’s look at two very common workarounds some have tried, and then we will talk about why you must go with a reputable developer when you make your final decision.

Leveraging Web Search Engines

One way a lot of companies have handled their need for an enterprise search platform is to simply use one like Google or some other web engine. On paper, this may seem to make a lot of sense. After all, who doesn’t trust Google? Most of us use it every single day and would be lost without the search engine giant. If you can leverage its power for your company’s needs, that would seem like a no-brainer, right?

Unfortunately, if you try this, you’ll learn the hard way that Google leaves a lot to be desired when it comes to this type of an environment. That’s not to say it won’t work; it just won’t work well and definitely not nearly as well as you want for a search engine working your company’s internal systems. Google and other web search engines are fantastic at what they’re designed to do. They are not designed to work in an enterprise search environment though. For that, you need a true enterprise search platform.

The major problem is that web search engines are all about sheer volume, which makes a lot of sense once you think about it. When you want to find a pizza parlor in your city, you want to know about every single option and then some. Google’s ability to harvest this much information and present it quickly is one of the reasons it’s so popular. Any search engine that can’t deliver this kind of volume is going to alienate users and soon be left on the scrap heap.

What web search engines like Google don’t do well, though, is carry out deep, detail-oriented searches. Again, this makes sense. Google is driven largely by keywords and backlinks. These are “surface searches” that weren’t created to do some of the tasks you need an enterprise search platform form.

Your employees may do some very simple searches, but they probably also have some pretty demanding queries too. Enterprise search platforms are designed to handle these types of searches and drill down to the last detail in any file or piece of data they come across. If your employees aren’t able to do these types of searches on a regular basis, the search software you invested in will be a waste of money. Worse, it could land you with all kinds of other problems because your people will think they’re doing the best possible search they can and concluding that what they want can’t be found.

Also, don’t forget that your company stores information in different places. Yes, it may all be on the same server, but in the digital world, there are various “silos” that hold onto information. Each silo is its own environment with its own rules. When you try using a web search engine to look through all your company’s silos, what will most likely happen is that it will have to go through one at a time. This is far from ideal, to say the least.

If you have a good number of silos, your employees will most likely give up. They won’t want to walk the search engine from one silo to the next like they’re holding onto the leash of a bloodhound. The whole point of a search engine is that it’s supposed to cut down on the exhaustive amount of “manual” work you’d otherwise have to do to find the data you need.

Silos aren’t all the same, so you want a search program that can go in and out of the type you have without requiring the employee to reconfigure their query.


Open Source Software


Another very popular method of acquiring enterprise search software is to go with an open source title. Once again, on paper, this seems like a very logical route to take. For one thing, the software is free. You can’t beat that price, especially when it comes to an enterprise-level platform. This usually seems like an unbeatable value for small- and medium-sized businesses that don’t have a lot of leeway where their budget is concerned.

That’s just one benefit that proponents of open source search engines tout though. There’s also the fact that you can modify the software as you see fit. This gives the user the ability to basically mold the code into a result that will fit their company’s needs like a glove.

There are a couple problems though. The first is that you get what you pay for. If your open source software doesn’t deliver, you have no one to complain to. You can always do your research to find a title that others have given positive reviews to. At the end of the day, though, don’t expect much in the way of support. There are plenty of forums to go through to find free advice, but that’s not the type of thing most professionals want to wade through.

When you go with a true, professional version, your employees will never be far from help if something goes wrong. Most companies these days have email and phone lines you can use, but many also have chat boxes you can open up on their website. None of these will be available for your company if you go with open source software.

Also, you can definitely modify the search engine software, but this isn’t necessarily unique to open-source platforms. Professional search platforms can be modified a number of ways, allowing the user to streamline their software and fine-tune the result so they get relevant results again and again.
This type of architecture, known as a pipeline, is becoming more and more the standard in this industry. Enterprise platforms come with all kinds of different search features, but that can also be a problem if they start getting in the way of one another. To ensure there are never too many cooks in the kitchen, pipeline architecture is used to line them all up, one in front of the other. By doing so, you’ll have a much easier time getting the search results you want, especially because you can just reconfigure these features as you see fit whenever you like.


Ongoing Updates Are Yours


One very important aspect of professional enterprise search platforms that is worth pointing out is that most developers are constantly putting out updates for their product. This is the same thing web search engines do, of course. Google, Yahoo and Bing all release upgrades constantly. The difference, however, is that enterprise platforms get upgrades that are specific to their purposes.
While there are updates for open source software, expect sporadic results. The developer of your favourite title could give up and go on to another project, leaving you to look for someone else to continue creating great updates.

If you have a skilled developer who is familiar with open source search engines on your team, this may be an attractive option. Still, most will find this route is just too risky. Most of us also don’t have that kind of developer on staff and it wouldn’t be worth it to hire someone on specifically for this reason (it’d be much more affordable to just buy professional software). Also, remember that, even if you do have this kind of talent within your ranks, you’ll soon become completely beholden to them if you start trusting them with this kind of job. Having someone who is completely responsible for your search engine being able to work and not having someone else on staff who can offer support or replace them is not a good idea.


Scalability Is a Given

Every company understands how important scalability is. This is especially true when it comes to software though. The scalability of a program can really make or break its value. Either it will turn into a costly mistake that greatly holds your business back or it will become the type of agile asset you actually take for granted, it’s so helpful.

Open source platforms are only as scalable as their code allows, so if the person who first made it didn’t have your company’s needs in mind, you’ll be in trouble. Even if they did, you could run into a problem where you find out that scaling up actually reveals some issues you hadn’t encountered before. This is the exact kind of event you want to avoid at all costs.

Now that you realize the importance of going with a reputable developer, your next step is picking which one to choose. You definitely won’t lack for options these days, so just take your time to ensure you go with the best one for your business.

Mike Miranda writes about enterprise software and covers products offered by software companies like www.rocketsoftware.com about topics such as Terminal Emulation, Legacy Modernization, Enterprise Search, Big Data and Enterprise Mobility.
Categories: DBA Blogs

Query existing HBase tables with SQL using Apache Phoenix

Thu, 2015-07-02 14:25
Spending a bit more time with Apache Phoenix and reading again my previous post I realised that you can use it to query existing HBase tables. That is NOT tables created using Apache Phoenix, but HBase - the columnar NoSQL database in Hadoop.

I think this is cool as it gives you the ability to use SQL on an HBase table.

To test this, let's say you login to HBase and you create an HBase table like this:

> create 'table2', {NAME=>'cf1', VERSIONS => 5}

The table2 is a simple table in HBase with one column family cf1 and now let's put some data to this HBase table.

> put 'table2', 'row1', 'cf1:column1', 'Hello SQL!'

then maybe add another row

> put 'table2', 'row4', 'cf1:column1', 'London'

Now, in Phoenix all you will have to do is create a database View for this table and query it with SQL. The database View will be read-only.  How cool is that, you don't even need to physically create the table or move the data to Phoenix or convert it, a database view will be sufficient and via Phoenix you can query the HBase table with SQL.

In Phoenix you create the view for the table2 using the same name. As you can see below the DDL used to create the view is case sensitive and if you created your HBase table name in lower case you will have to put the name in between double quotes.

So login to Phoenix and create the "table2" view like this:

> create view "table2" ( pk VARCHAR PRIMARY KEY, "cf1"."column1" VARCHAR );

And here is how you then query it in Phoenix:


SQL Query on Phoenix
Tremendous potential here, imagine all those existing HBase tables which now you can query with SQL. More, you can point your Business Intelligence tools and Reporting Tools and other tools which work with SQL and query HBase as if it was another SQL database.

A solution worth investigating further? It definitely got me blogging in the evenings again.

To find out more about Apache Phoenix visit their project page https://phoenix.apache.org/



Categories: DBA Blogs

Apache Phoenix, SQL is getting closer to Big Data

Tue, 2015-06-30 16:50


Here is a post about another project in the Big Data world, like Apache Hive from my previous post, enables you to do SQL on Big Data. It is called Apache Phoenix.

Phoenix is a bit different, a bit closer to my heart too, as I read the documentation on Apache Phoenix, the word 'algebra' and 'relational algebra' came across few times, and that mean only one thing, SQL! The use of the word algebra in the docs did give me a lot of confidence. SQL has closure, is based on a database systems model which has it's roots in logic and maths and especially a subset of algebra, The Set Theory.

Apache Phoenix is developed in Salesforce and is now one of the popular projects in Apache. Apache Phoenix is a SQL skin on top of HBase, the columnar (NoSQL) database of the Hadoop ecosystem, capable of storing very large tables and data and query them via 'scans'. HBase is part of the Hadoop ecosystem and the file system it uses is usually HDFS. Apache Phoenix is using JDBC on the client as a driver.

In the race to bring the easiest to use tools for Big Data, I think Apache Phoenix is very close. It is the SQL we know used since the 1970s. The Apache Phoenix team seems to be committed and willing to introduce all of the missing parts of SQL, including transaction processing with different isolation levels.  Making Phoenix a fully operational Relational Database layer on HBase. Have a look in their roadmap. The amount of current and suggested future SQL compatibility is remarkable, and this makes me take them really seriously.
  • Transactions
  • Cost-based Query Optimization! (Wow)
  • Joins
  • OLAP
  • Subqueries
  • Striving for full SQL-92 compliance
In addition to all this, it is also possible to turn an existing HBase table to an Apache Phoenix table using CREATE TABLE or even CREATE VIEW, the DDL statements that we know. How handy is that? Suddenly you can SQL enable your existing HBase database!

How to install and use Phoenix

The SQL skin can be installed to an existing Hadoop HBase installation very quickly. All you need to do is to download and extract the tarball. You can setup a standalone Hadoop environment, look at my previous blog post for that, and then install HBase and install Apache Phoenix

Once the Apache  Phoenix software is installed, then you can start it and query it with SQL like this.

From within the bin/ directory of Phoenix install directory run

$ ./sqlline.py  localhost

That will bring you to the phoenix prompt


0: jdbc:phoenix:localhost> select * from mytable;


Categories: DBA Blogs

Hive (HiveQL) SQL for Hadoop Big Data

Thu, 2015-06-25 14:30


In this  post I will share my experience with an Apache Hadoop component called Hive which enables you to do SQL on an Apache Hadoop Big Data cluster.

Being a great fun of SQL and relational databases, this was my opportunity to set up a mechanism where I could transfer some (a lot)  data from a relational database into Hadoop and query it with SQL. Not a very difficult thing to do these days, actually is very easy with Apache Hive!

Having access to a Hadoop cluster which has the Hive module installed on, is all you need. You can provision a Hadoop cluster yourself by downloading and installing it in pseudo mode on your own PC. Or you can run one in the cloud with Amazon AWS EMR in a pay-as-you-go fashion.

There are many ways of doing this, just Google it and you will be surprised how easy it is. It is easier than it sounds. Next find links for installing it on your own PC (Linux).  Just download and install Apache Hadoop and Hive from Apache Hadoop Downloads

You will need to download and install 3 things from the above link.

  • Hadoop (HDFS and Big Data Framework, the cluster)
  • Hive (data warehouse module)
  • Sqoop (data importer)
You will also need to put the connector of the database (Oracle, MySQL...) you want to extract data from in the */lib folder in your Sqoop installation. For example the MySQL JDBC connector can be downloaded from hereDon't expect loads of tinkering installing Apache Hadoop and Hive or Sqoop, just unzipping binary extracts and few line changes on some config files in directories, that's all. Is not a big deal, and is Free. There are tones of tutorials on internet on this, here is one I used from another blogger bogotobogo.


What is Hive?

Hive is Big Data SQL, the Data Warehouse in Hadoop. You can create tables, indexes, partition tables, use external tables, Views like in a relational database Data Warehouse. You can run SQL to do joins and to query the Hive tables in parallel using the MapReduce framework. It is actually quite fun to see your SQL queries translating to MapReduce jobs and run in parallel like parallel SQL queries we do on Oracle EE Data Warehouses and other databases. :0) The syntax looks very much like MySQL's SQL syntax.

Hive is NOT an OLTP transactional database, does not have transactions of INSERT, UPDATE, DELETE like in OLTP and doesn't conform to ANSI SQL and ACID properties of transactions.


Direct insert into Hive with Apache Sqoop:

After you have installed Hadoop and have hive setup and are able to login to it, you can use Sqoop - the data importer of Hadoop - like in the following command and directly import a table from MySQL via JDBC into Hive using MapReduce.

$  sqoop import -connect jdbc:mysql://mydatbasename -username kubilay -P -table mytablename --hive-import --hive-drop-import-delims --hive-database dbadb --num-mappers 16 --split-by id

Sqoop import options explained:
  •  -P will ask for the password
  • --hive-import which makes Sqoop to import data straight into hive table which it creates for you
  • --hive-drop-import-delims Drops \n\r, and \01 from string fields when importing to Hive. 
  • --hive-database tells it which database in Hive to import it to, otherwise it goes to the default database. 
  • --num-mappers number of parallel maps to run, like parallel processes / threads in SQL
  • --split-by  Column of the table used to split work units, like in partitioning key in database partitioning. 
The above command will import any MySQL table you give in place of mytablename into Hive using MapReduce from a MySQL database you specify.

Once you import the table then you can login to hive and run SQL to it like in any relational database. You can login to Hive in a properly configured system just by calling hive from command line like this:

$ hive
hive> 


More Commands to list jobs:

Couple of other commands I found useful when I was experimenting with this:

List running Hadoop jobs

hadoop job -list

Kill running Hadoop jobs

hadoop job -kill job_1234567891011_1234

List particular table directories in HDFS

hadoop fs -ls mytablename


More resources & Links



Categories: DBA Blogs

Oracle APEX_WEB_SERVICE REST API call

Wed, 2015-03-04 12:15
In this post I will try to show you how I used the Oracle Apex and the APEX_WEB_SERVICE  PL/SQL package to quickly send a request to a public Internet API and how I handled the response. The code below was written during a 'Hackday' and hasn't been extensively tested.

My use case is integrating Oracle Apex with the public Mendeley REST API for Mendeley Catalog Search.

The idea was to build an application in Oracle Apex to query the Mendeley REST API Catalog with a keyword. Mendeley REST API gives JSON response so I used PL/JSON to parse it.  I hear in Oracle 12c JSON is going to be a native data-type. My Oracle Apex host is running Oracle 11g and I had to use PL/JSON for ease.

To cut it short here is how the Mendeley Catalog Search on Oracle Apex application look  like. (Click image to go to app or visit http://apex2.enciva.co.uk/apex/f?p=864:2






To integrate with Mendeley REST API from Oracle Apex, I used one PL/SQL function and one procedure.

I used the function to obtain the Mendeley REST API Client Credentials Authorisation flow token and the procedure to do make the API request to Mendeley Catalog Search and to handle the response.

Here is the MENDELEY_CALL PL/SQL function I created:

This function returns the Client Credentials Authorisation Flow token from the Mendeeley REST API

create or replace function mendeley_call (p_id in varchar2)
return varchar2
is
v_token varchar2(1000);
token varchar2(1000);
jtoken json;
v_grant_type varchar2(400:= 'client_credentials';
v_client_id varchar2(500:= p_id;
v_client_secret varchar2(500:= '<put_your_mendeley_client_secret_here>';
v_scope varchar2(300:= 'all';
begin

/*----------Setting Headers----------------------------------------*/                                      
apex_web_service.g_request_headers(1).name := 'Content-Type';
apex_web_service.g_request_headers(1).Value := 'application/x-www-form-urlencoded; charset=utf-8';
/*-----------------------------------------------------------------*/

token := apex_web_service.make_rest_request
    (
      p_url         => 'https://api.mendeley.com/oauth/token'
    p_http_method => 'POST'
    p_parm_name   => apex_util.string_to_table('grant_type:client_id:client_secret:scope')
    p_parm_value  => apex_util.string_to_table(v_grant_type||':'||v_client_id||':'||v_client_secret||':'

||v_scope)
    p_wallet_path => 'file:/home/app/oracle/product/11.2.0/dbhome_1/owm/wallets/oracle'
    p_wallet_pwd  => '<put_your_oracle_wallet_password_here>'
    );
-- debug
-- dbms_output.put_line(token);
jtoken := json(token);
v_token := json_ext.get_string(jtoken,'access_token');
-- debug
-- dbms_output.put_line(v_token);
return v_token;
EXCEPTION
WHEN OTHERS THEN
   raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
end;



Here is the anonymous procedure which I put into a PL/SQL region on the Oracle Apex page:

This procedure incorporates the function above and makes the request and handles the response from the Mendeley REST API

Note how the procedure calls the function MENDELEY_CALL (above) to load the variable v_token. 

DECLARE
  v_token  VARCHAR2(599:= mendeley_call(put_your_mendeley_client_id_here);
  v_search VARCHAR2(500);
  mendeley_document NCLOB;
  v_status VARCHAR2(100);
  obj json_list;
  v_id VARCHAR2(100);
  v_title NVARCHAR2(1000);
  v_abstract NCLOB;--varchar2(32000);
  v_link     VARCHAR2(1000);
  v_source   VARCHAR2(500);
  v_type     VARCHAR2(100);
  v_pct_hit  VARCHAR2(10);
  v_rows     NUMBER(10);
  v_batch_id NUMBER(10);
BEGIN
  -- Oracle Wallet
  utl_http.set_wallet('file:/home/app/oracle/product/11.2.0/dbhome_1/owm/wallets/oracle'

'my_secret_password');
  -- Set Authorisation headers and utf8
  -- the following lilne is necessary if you need to use languages other than latin and 
  -- you will use APEX_WEB_SERVICE package 
  utl_http.set_body_charset('UTF-8');
  -- build the Authorisation header
  apex_web_service.g_request_headers(1).name  := 'Content-Type';
  apex_web_service.g_request_headers(1).value := 'application/jsonrequest';
  apex_web_service.g_request_headers(1).name  := 'Authorization';
  apex_web_service.g_request_headers(1).value := 'Bearer '||v_token||'';
  
  -- Make the request and load the response into a CLOB 
  mendeley_document := apex_web_service.make_rest_request 
      
        p_url => 'https://api.mendeley.com:443/search/catalog' 
      p_http_method => 'GET' 
      p_parm_name => apex_util.string_to_table('title:limit'
      p_parm_value => apex_util.string_to_table('Mendeley:10'
      );
  -- Load the response to JSON_LIST PL/JSON object
  obj := json_list(mendeley_document);
  -- Start extracting values from the JSON and writhe some HTML
  -- Traverse over JSON_LIST extract elements you like
  FOR IN 1..obj.count
  LOOP
    v_id       := json_ext.get_string(json(obj.get(i)),'id');
    v_title    := json_ext.get_string(json(obj.get(i)),'title');
    v_abstract := json_ext.get_string(json(obj.get(i)),'abstract');
    v_link     := json_ext.get_string(json(obj.get(i)),'link');
    v_source   := json_ext.get_string(json(obj.get(i)),'source');
    v_type     := json_ext.get_string(json(obj.get(i)),'type');
    -- write extracted data
   dbms_output.put_line(v_title||' ==> '||v_abstract);
   END LOOP;
 END;

 END;

This shows how easy is, in this case using one function and one procedure to make a REST API request to an external Web Service from Oracle Apex. 
Categories: DBA Blogs

Test MySQL on AWS quickly

Thu, 2015-02-19 02:20
Using sysbench to performance test AWS RDS MySQL hardware is an easy three step  operation. Sysbench creates synthetic tests and they are done on a 1 mil row 'sbtest' table that sysbench creates in the MySQL database you indicate. The test doesn't intrude with your database schema, and it doesn't use your data, so it is quite safe. The test is an OLTP test trying to simulate event operations in the database as it runs various, SELECT, INSERT, UPDATE and DELETE requests on it's own 'sbtest' table.The results  of the tests are metrics like transactions per second, number of events, elapsed time etc. See man pages for description and Google it, it is a  popular testing tool. Other things you can set it up to do are to control how many requests (events) you want it to execute in a given time or you can tell it to keep on executing infinite requests until you stop it, or destruction testing. Is a very flexible testing tool with many options including throtling concurrency.You can be up and running with 3 commands on a unix system as follows.  Download sysbench tool (doing this on ubuntu)sudo apt-get install sysbenchCreate a table with 1 mil rowssysbench --test=oltp --oltp-table-size=1000000 --mysql-host={your rds host url} --db-driver=mysql --mysql-user={your rds root user} --mysql-password={password} --mysql-db={your mysql database name} prepareTest with different parameterssysbench --test=oltp --oltp-table-size=1000000 --mysql-host={your rds host url} --db-driver=mysql --mysql-user={your rds root user} --mysql-password={password} --mysql-db={your mysql database name} --max-time=60 --num-threads=550 runWarning: Synthetic  tests will just give you the ability of the hardware at a given standard set of requests and DML operations. There are no way an indication of what will happen to your database if the real workload increases beacause of the applications. Application Load Testing is something else, applications are  complex!  Database Workload is dependent on the application generated workload from real users using the system and is very hard to simulate that in a test. It is not imppossible If you use a database, such as Oracle which has the capability of recording and replaying its production database workload - called Automatic Workload Repository (AWR). In MySQL I couldn't find  so far a way to do this. But sysbench synthetic tests gave me the ability to quickly benchmark and baseline a MySQL database capabilities on different AWS Amazon hardware, something is better than nothing I suppose. 
Categories: DBA Blogs

MySQL Locking

Tue, 2015-01-06 03:53
MySQL and Row Level Locking? Or why are you getting the error:

ERROR 1205 (HY000) : Lock wait timeout exceeded; try restarting transaction

You get the error because your allocated time to hold a DML lock in a transaction exceeds the set limit. Usually the default limit to hold a DML row lock, set by innodb_lock_wait_timeout db parameter, is 50 seconds. If your transaction doesn't commit/rollback within 50 seconds you will get this error. We don't want to hold locks for longer than 50 seconds anyway, throughput would be affected.

And yes MySQL in innodb uses row level locking. Since MySQL 5.1+ (time Oracle took over) it does row level locking in its InnoDB tables. That means only the rows which are selected . . . FOR UPDATE . . . are locked and not the  whole table. To see the threads (sessions) which are locking other threads and which queries are locking, use the following INFORMATION_SCHEMA dictionary SQL query as DBA.  You will be able to see blockers and waiters of transactions  waiting on locks. Run it as is using INFORMATION_SCHEMA schema, no modifications.

Use this SQL query to monitor locks and transactions and note that query will return data only when there are locks!


SELECT 
    r.trx_id AS wating_trx_id,
    r.trx_mysql_thread_id AS waiting_thread,
    TIMESTAMPDIFF(SECOND,
        r.trx_wait_started,
        CURRENT_TIMESTAMP) AS wait_time,
    r.trx_query AS waiting_query,
    l.lock_table AS waiting_table_lock,
    b.trx_id AS blocking_trx_id,
    b.trx_mysql_thread_id AS blocking_thread,
    SUBSTRING(p.host,
        1,
        INSTR(p.host, ':') - 1) AS blocking_host,
    SUBSTRING(p.host,
        INSTR(p.host, ':') + 1) AS blocking_port,
    IF(p.command = 'Sleep', p.time, 0) AS idle_in_trx,
    b.trx_query AS blocking_query
FROM
    information_schema.innodb_lock_waits AS w
        INNER JOIN
    information_schema.innodb_trx AS b ON b.trx_id = w.blocking_trx_id
        INNER JOIN
    information_schema.innodb_trx AS r ON r.trx_id = w.requesting_trx_id
        INNER JOIN
    information_schema.innodb_locks AS l ON w.requested_lock_id - l.lock_id
        LEFT JOIN
    information_schema.PROCESSLIST AS p ON p.id = b.trx_mysql_thread_id
ORDER BY wait_time DESC;

Categories: DBA Blogs

API Integration with Zapier (Gmail to Salesforce)

Sun, 2014-09-07 12:42
Recently I attended a training session with +General Assembly  in London titled, What and Why of APIs. It was a training session focusing on usage of APIs and it was not technical at all. I find these type of training sessions very useful as they describe concepts and controlling ideas behind technologies rather than the hands-on, involved implementation details.

What grabbed my attention from the many different and very useful public and private API tools, 'thingies', introduced in this training session was Zapier. - www.zapier.com

Zapier looked to me as a platform for integrating APIs with clicks rather than code, with declarative programming. Is a way of automating the internet. What you get when you sign up with them is the ability to use 'Zaps', or create your own zaps. Zaps are integration of endpoints, like connecting Foursquare to Facebook or Gmail to Salesforce and syncing them. One of the Zaps available does that, connects your Gmail emails to Salesforce using the Gmail and Salesforce APIs and lets you sync between them. Not only that, but Zapier Zaps also put triggers on the endpoints which allow you to sync only when certain conditions are true. For example the Gmail to Salesforce Zap can push your email into a Salesforce Lead only when an email with a certain subject arrives to your gmail inbox. This is what a Zapier platform looks like:


An individual Zap looks like this and is nothing more than a mapping of the Endpoints with some trigger actions and filters.


The environment is self-documenting and very easy to use. All you do is drag and drop gmail fields and match them with the Lead, or other custom object Salesforce fields. Then you configure the sync to happen only under certain conditions/filters. Really easy to set-up. The free version runs the sync every 5 hours, well good enough for me. The paid version runs the sync every 5 minutes. 

There is even capability to track historical runs and trigger a manual run via the Zap menu. See below the 'Run' command to run a Zap whenever you like. 


In my case I used the tool to create a Zap to do exactly what I just described. My Zap creates a Salesforce Lead automatically in my Salesforce org whenever a 'special' email is sent to me. Great automation!

This is a taste of the 'platform cloud' tools out there to do API to API and App to App integrations with clicks and not code. With tools like Zapier all you really need is, imagination!

More links:

Categories: DBA Blogs

Pages