This newsletter includes:
- i can do BI with IBM i
- SQL for IBM i System Admins?
- Release levels and PTFs
It’s almost Thanksgiving here in the US, where did the year go? It’s been a great year here at iTech Solutions. We have moved into new offices, increased our staff, and we are working with many new customers and helping our existing customers. We have also become an IBM Premier Business Partner. We have a lot to be thankful for, and are glad that you gave us the opportunity to work with you. If there is anything that we do that doesn’t exceed your expectations, please contact me directly. I hope with the upcoming holidays approaching you are able to spend it with your family and friends, and that you have much to be thankful for. As many of our readers over the years know, this will be our last newsletter of the year. In December, we publish the much anticipated How iTech Solutions helped Santa save Christmas newsletter. I hear there are some new POWER8 stories to be told, so get your stockings hung by the fire and the December issue will be out within a month.
IBM has announced that IBM i 6.1 (as well as 6.1.1) will end support on Sept 30, 2015. If you are still on 6.1, please contact us and arrange for one of our System Admins to come and upgrade your operating system. Of course, it doesn’t matter what release you are on, we can help you upgrade to the latest release IBM i 7.2 If you are still back on V5R4, we can help you with that upgrade as well. We have the skills, the experience, and the knowledge to easily upgrade your operating system so you don’t have to worry.
Our New England 10 city roadshow has been quite successful so far, with just 1 additional stop left on this tour in Manchester NH on December 18. We have enjoyed the time spent talking with customers on some of the new features, speeds, and functionality in the Scale-Out (Low end) Power8 servers, and also tell about all the new features and functions that were delivered in IBM i 7.2. IBM i 7.2 has some amazing features that will provide quite a performance boast to most environments. We received very good feedback from the attendees. We look forward to a few more we are putting together in additional cities in 2015. If you missed one, and would like a session customized for you on Power8 and IBM i 7.2, please let us know and we will come to your company and present it to you.
This issue of our newsletter has 6 articles. In the first article, we will discuss using DB2 WebQuery with the great reporting enhancements it has to report on your data. The second article is on iTech Solutions Managed Services to help you with your IBM i Administration and Support. The third article is about using SQL to show you which user profiles have *ALLOBJ authority. The fourth is on the iTech Solutions POWER8 and IBM i 7.2 Roadshow coming to your area. The fifth article lists some of the upcoming events in which iTech Solutions will be participating. The last article is for your reference with updated PTF information. Please note that for all 7.1 customers that are on the Quarterly or Semi-annual iTech Solutions PTF maintenance plan, we will be installing 7.1 Technology Refresh 9 for you on your next application of PTFs. For the 7.2 customers, we will be installing 7.2 Technology Refresh 1.
Having a business partner isn’t the same as having iTech Solutions, if you aren’t getting the support, the help, the guidance, and the advice you need to succeed then you need to contact iTech Solutions for your IBM Power Systems running IBM i needs. We can help you upgrade your AS/400 or iSeries to a Power Systems running IBM i, or even your existing Power5, Power6, or Power7 machines to Power8.
iTech Solutions vast experience can help you improve performance, perform security audits; implement a High Availability solution; perform health checks, systems management, remote administration, PTF management, blade installations, Cloud based systems, Hosting, replication, and backup/recovery; upgrade an existing machine; or upgrade to a new machine. If you are thinking of LPAR or HMC, then think iTech Solutions. We have the skills to help you get the most out of your IBM i.
For more information on any of the articles below please visit us on the web at iTech Solutions or email iTech Solutions. We would love for you to let us know any articles that you wish for the future, or if you enjoy any of the articles in the current newsletters.
Are you using Query/400 for reporting? There is another way to provide information to your organization for decision-making. IBM and Information Builders Inc. have teamed-up to provide Business Intelligence offerings on IBM i with a product called DB2 Web Query for i. Now available in version 2.1, this tool-set provides powerful reporting and ease of use.
Web Query provides data retrieval and summarization for reporting or charting, in a web browser from your DB2 database. You can access existing physical and logical files or SQL tables, views or stored procedures. WQ also provides a metadata layer. This can be utilized, when needed, to pre-establish joins between tables, give more meaning to database field names, enhance field descriptions or even convert data types (i.e. from numeric to date) for more functionality.
Web Query 2.1 has been around since 2012 and has been enhanced to keep pace with other IBM i enhancements. In a world where the words Cloud, Analytics, Mobile and Social are heard often, we can now provide access to reports over the IBM i database from browsers and mobile clients. DB2 Web Query for i comes in Express or Standard editions, where Express enables you to do basic reporting and Standard adds report scheduling, distribution and run time group licensing.
While Query/400 was good at producing green-screen reports containing data and numbers in 80 or 132 columns, Web Query goes further, enabling reports, charts, drill-down analysis via both connected and disconnected reporting. Browser based presentation means reports can have Headers and Footers, Subtotals, Breaks with user selected Themes Styles or Colors. A number of pre-designed Themes are provided.
Web Query Reports and Charts can pass and receive parameters, allowing you to prompt for input or link reports, enabling drill-down from summary to detail levels. Web Query can generate reports in a variety of formats including HTML, PDF or EXCEL, as well as charts and graphs of all the usual types (pie, bar, line, scatter, bubble, surface, geographic maps, cloud) in HTML (or HTML5) plus active reports that enable disconnected analysis functions.
Some example scenarios:
- Using WQ to push reports to a laptop or tablet, field sales personnel could receive sales reports while traveling. They could slice and dice data while disconnected, in a plane, at the customer location or while connected from the road.
- Executives might review a scorecard of key metrics, on a daily basis, via a web page or portal. Web Query and DB2 can summarize data nightly for review first thing in the morning.
- Create a catalog of pre-defined reports, allowing users to select and customize portal content based on their interest or business role.
- For finance power-users, generate an Excel spreadsheet from DB2 data following the end of a fiscal period. Web Query can do this with its report distribution facility, including Excel formulas or not, as needed.
- For daily inquiry purposes, Web Query’s InfoAssist report designer is easier to use than Query/400 and can generate robust reports and charts.
- If your organization is seeking “One version of the Truth!” about business data from your IBM i systems? Web Query can enable you to provide data access from files or ERP systems, creating reports or charts at summary and detail levels. You don’t need to install any other server platforms as WQ resides on IBM i.
Times change, and so does what should be done on your system, or perhaps in this case what tools that you can take advantage of. If you are running your IBM i like it was 1990 or an AS/400 perhaps it is time to look at WebQuery. Contact iTech Solutions and we have the resources to help you get started with WebQuery.
IBM i Managed Services .
Focused on building longstanding relationships with our clients using a straightforward, no-nonsense approach, iTech Solutions specializes in providing advanced IBM i systems management and technical support services. Our motivated team delivers innovative professional and technical solutions so you don’t have to worry about the performance, management, and administration of your IBM i. Our goal is simple: We will manage your IBM i, you perform the other IT functions to enhance the business.
What can iTech’s Managed Services Offer your Business?
Technology drives business performance in today’s modern world, yet not every company has the resources or expertise to maintain an efficient, up-to-date, and secure IBM i infrastructure. Between budgetary restraints, high operating costs, and downtime associated with non-performing equipment, many small and mid-size businesses are facing major obstacles in IT. Let iTech Solutions manage your IBM i, while you manage and run your business.
iTech Solutions presents flexible Managed Services offerings that enable organizations access to world-class IT experts, training, support, maintenance, planning, and monitoring. Our Managed Services are tailored to offer complete IBM i infrastructure support, yet versatile enough to supplement existing IT staffs. With customizable support options, clients only gain the services that they need to receive faster, better-performing IT environments and the greatest returns for their value.
Our Managed Services offerings include:
Systems Administration Services: IT infrastructures have many complex working pieces. With our Systems Administration Services, our experts offer direct resolution for systems and/or hardware error occurrences. This service also provides 24x7x365 administrative support, as well as diagnostic evaluations.
Annual IBM Future Directions Executive Briefing: Gain insight into your IBM i infrastructure’s potential. iTech Solutions offers annual executive briefings, where our experts analyze your current IBM i environment and then plot out a course for the most valuable areas of growth in the coming year. iTech will help ensure your company makes sound technology investments while presenting the best technology improvements for your business.
System Capacity Management Monitoring: Efficient storage environments are an essential element in every well-functioning IT Department, yet many SMBs don’t have the manpower to perform thorough, daily monitoring. iTech Solutions provides System Capacity Management Monitoring, which consistently checks system processors, as well as memory and disk utilization, messages, jobs, basically everything on your IBM i. If your company ever approaches any critical thresholds, then our team alerts your staff and plans for corrective action.
System Performance Tuning Monitoring: Between ad-hoc fires that sprout up, maintenance, and backup schedules, the day-to-day performance diagnostics of IBM i systems can sometimes fall between the cracks. Our experts & software perform continuous System Health and Performance checks, as well as a semi-annual review to report on the statuses of your systems.
System Monitoring and Support Services: Many SMBs do not possess the extensive staffs that large enterprises have at their disposal. With iTech’s Systems Monitoring and Support Services, your company can gain remote system monitoring, 365 days a year. Our experts ensure that your systems are up to date and performing. With support options, you always have a link to our on-staff specialists.
Individual & Group PTF Installations: Program Temporary Fixes (PTFs) are offered when solutions to known software problems are made available, or when new updates are released to improve software and system functionality. PTF updates are essential for the Operating System to stay up to date, secure, and functioning at optimum capacity, yet they are oftentimes complex and avoided. To avoid impacting the production systems or schedules, iTech Solutions offers PTF management. With a trained team that understands the intricacies of IBM i dependencies and prerequisites, we can perform PTFs updates and improve the performance and security of your IBM i.
Let the experts manage and administer your system. Contact iTech Solutions and let them handle the problems and get your system beyond where you would expect.
Did you know that DB2 for i can be used to achieve better security, improved systems management and much more? A growing list of DB2 for i “services” have been provided as alternative ways to access IBM i operating system detail. I had the pleasure to sit down with Scott Forstie from IBM Rochester, who is the DB2 for i Business Architect. Scott has been busy the last few years bringing new functionality into IBM i 7.1 via Database Group PTFs, and working diligently adding lots of new DB2 features and functions to 7.2 (Row and Column Access Control is one you probably heard of with 7.2). There are many new features being introduced into IBM i that are very useful for System Administrators. We usually don’t associate SQL with IBM i admins, but now the operating system is collecting all this great information in tables, that we can query.
Scott shared an example of one just SQL statement, which I can see a use for at every site. Excess authority, too many users have *ALLOBJ authority. With IBM i 7.2, DB2 for i has new capabilities to reduce the need to *ALLOBJ (via QIBM_DB_SECADM function usage) and to limit access to data within rows & columns of a database file. Yet, you still need to know just who has *ALLOBJ authority. The following query returns the full set of users who have *ALLOBJ user special authority, either directly in their profile or indirectly through a group profile.
SELECT AUTHORIZATION_NAME, STATUS, NO_PASSWORD_INDICATOR, PREVIOUS_SIGNON,
TEXT_DESCRIPTION FROM QSYS2.USER_INFO WHERE SPECIAL_AUTHORITIES LIKE ‘%*ALLOBJ%’ OR AUTHORIZATION_NAME IN ( SELECT USER_PROFILE_NAME FROM QSYS2.GROUP_PROFILE_ENTRIES WHERE GROUP_PROFILE_NAME IN ( SELECT AUTHORIZATION_NAME FROM QSYS2.USER_INFO WHERE SPECIAL_AUTHORITIES like ‘%*ALLOBJ%’ ) ) ORDER BY AUTHORIZATION_NAME
WOW, that is some query. This is available with the base release of 7.2, introduced to 7.1 with SF99701 level 26, and introduced to 6.1 with SF99601 level 31. This is the kind of information you need to better manage your machine. To execute this, from a command line type STRSQL, and then copy the SQL statement above into the editor and run it. You will be impressed with the results and hopefully can better lock down your machine.
If you need help getting up to the latest set of PTFs, help managing your IBM i, or upgrading to the latest release, contact iTech Solutions.
Join us for the iTech Solutions Power8 and IBM i 7.2 Roadshows where you can learn all about IBM’s recent announcements of the Power8 servers and the enhancements delivered in IBM i 7.2. These announcements emphasize IBM’s commitment to the Power systems platform and the IBM i operating system.
iTech Solutions will present multiple sessions at each of these roadshow locations. The first session covers the Power8 servers and how they can significantly improve your organizations processing capabilities. The second session covers the announcement of enhancements that are available in IBM i 7.2. The third session will be on Cloud Computing for IBM i, Managed Services, and High Availability as a Service.
- Power8 processors and I/O enhancements
- New features, speeds, and functions
- IBM i 7.2 enhancements to Security, Application Development, and Performance
- IBM’s commitment to Power Systems and IBM i Roadmap
iTech Solutions Roadshow schedule:
Tuesday September 16, 2014 Crown Plaza Hartford-Cromwell, Cromwell, CT
Monday September 22, 2014 IBM Facility 590 Madison Ave New York City
Tuesday September 23, 2014 Marriott Hotel, Providence, RI
Wednesday September 24, 2014 Fireside Inn & Suites, Portland ME
Tuesday September 30, 2014 Fireside Inn & Suites ,W. Lebanon, NH
Thursday October 02, 2014 Double Tree Hotel, Tarrytown, NY
Thursday October 02, 2014 Norwalk Inn, Norwalk, CT
Wednesday November, 05, 2014 Forefront Conf. Center, Waltham, MA
Thursday November 06, 2014 Marriott Hotel, Springfield, MA
Check our web site http://www.itechsol.com/events for up to date information on these venues and to register for the presentations closest to you. If you missed a session, and would like us to provide you with a private presentation, just contact us and we can schedule one for you at your location.
You won’t want to miss these informative sessions presented by iTech Solutions’ Pete Massiello.
Some of the events that we will be speaking at or exhibiting at are listed below. Don’t forget the iTech Solutions web site at http://www.itechsol.com.
November 19, 2014 – New England JDE User Conference Gillette Stadium, Foxboro, Massachusetts
Come visit Rick & Paul at our booth
December 9 – What you need to know to Upgrade IBM i to 7.2 Get link XXXXX
March 3 & 4, 2015 – COMMON and iInTheCloud present IBM i Systems Management with Pete Massiello and Larry Bolhuis right by the Chicago O’Hare airport. More details on sessions to follow.
April 26 to 29th – COMMON Annual Conference. Stop by our booth visting Rick and Paul, or hear Pete Massiello speak on sessions TBD.
People are always asking me how often they should be performing PTF maintenance, and when is the right time to upgrade their operating system. I updated this article from last month with the current levels of PTFs. Let’s look at PTFs. First, PTFs are Program Temporary Fixes that are created by IBM to fix a problem that has occurred or to possibly prevent a problem from occurring. In addition, some times PTFs add new functionality, security, or improve performance. Therefore, I am always dumbfounded as to why customers do not perform PTF maintenance on their machine at least quarterly. If IBM has come out with a fix for your disk drives, why do you want to wait for your disk drive to fail with that problem, only to be told that there is a fix for that problem, and if you had applied the PTF beforehand, you would have averted the problem. Therefore, I think a quarterly PTF maintenance strategy is a smart move. Many of our customers are on our quarterly PTF maintenance program, and that provides them with the peace of mind of knowing their system is up to date on PTFs. Below is a table of the major group PTFs for the last few releases. This is what we are installing for our customers on iTech Solutions Quarterly Maintenance program.
The easiest way to check your levels is to issue the command WRKPTFGRP. They should all have a status of installed, and you should be up to the latest for all the above, based upon your release. Now there are more groups than the ones listed above, but these are the general ones that most people require. We can help you know which group PTFs you should be installing on your machine based upon your licensed programs. Here is a nice tidbit. The Cumulative PTF package number is broken down as YDDD, where Y is the year and DDD is the day it was released. Therefore, if we look at the cumulative package for V7R1, the ID is 13287. We can determine that it was created on the 287th day of 2013, which is October 14, 2013. Look at your machine and this will give you a quick indication of just how far out of date in PTFs you may be. I left V5R1 & V5R2 off the list, because if you are on V5R1 or V5R2, you don’t need to be worrying about PTFs, you really need to be upgrading your operating system. The same can be said for V5R3 & V5R4, but there are still customers who are on those releases.
If you have a Hardware Management Console (HMC,) you should be running:
|HMC||V8R8.1||SP1 MH01420 then MH0147, then MH01468
* Can’t use with Power5 hardware
|HMC (CR4 last release)||V7R7.9||SP 1 MH01428, then MH1473, &
|HMC||V7R7.8||SP 1 MH01397,then MH01472,
|or||V7R7.7||SP 3 MH1397, then SP 4 MH01415,
If we have a model listed above in the HMC column that is the highest level of firmware that model of the HMC can be upgraded to.
- Note that release 8.8.1 does not support any Power5 servers.
- Version 7.7.9 is not supported and cannot be installed on HMC models C03, C04 or CR2.
- If an HMC is used to manage any POWER7 processor based server, the HMC must be a model CR3 or later model rack-mount HMC or C05 or later desk side HMC.
- HMC V8R8.1 is supported on rack-mount models CR5, CR6, CR7 and CR8; and on desktop model C08. These listed models meet or exceed the V8R8.1 minimum memory requirement of 2GB however 4GB is recommended.
- If you want to manage a POWER8 machine, you need to be on HMC 8.8.1
If you have an Flexible Service Processor (FSP) your firmware should be:
|Power5 or 5+||520, 515, 525, 550, 570||SF240_418||last|
|Power6||940x, M15, M25, M50||EL350_159||last|
|8203-E4A, 8204-E8A, 8204-E4A||EL350_159||last|
|MMA, 560, 570||EM350_159||last|
|Power7||8231-E1B, 8202-E4B, 8231-E2B, 8205-E6B, 8233-E8B, 8236-E8C||AL730_142|
|8231-E1C, 8202-E4C, 8205-E6C||AL740_152|
|Power7+||8231-E1D, 8202-E4D, 8231-E2D, 8205-E6D||AL770_090|
|Power8||8247-21L, 8247-22L, 8284-22A, 8286-41A, 8286-42A||SV810_101|
If you need help with upgrading your HMC or FSP just give us a call. We will be happy to perform the function for you or assist you in doing it. Contact Pete Massiello.