Sales Tel: +63 945 7983492  |  Email Us    
SMDC Residences

Air Residences

Features and Amenities

Reflective Pool
Function Terrace
Seating Alcoves

Air Residences

Green 2 Residences

Features and Amenities:

Wifi ready study area
Swimming Pool
Gym and Function Room

Green 2 Residences

Bloom Residences

Features and Amenities:

Recreational Area
2 Lap Pools
Ground Floor Commercial Areas

Bloom Residences

Leaf Residences

Features and Amenities:

3 Swimming Pools
Gym and Fitness Center
Outdoor Basketball Court

Leaf Residences

Contact Us

Contact us today for a no obligation quotation:


+63 945 7983492
+63 908 8820391

Copyright © 2018 SMDC :: SM Residences, All Rights Reserved.


































































A2090-545 dumps with Real exam Questions and Practice Test - smresidences.com.ph

Great Place to download 100% free A2090-545 braindumps, real exam questions and practice test with VCE exam simulator to ensure your 100% success in the A2090-545 - smresidences.com.ph

Pass4sure A2090-545 dumps | Killexams.com A2090-545 real questions | http://smresidences.com.ph/

A2090-545 Assessment: DB2 9.7 SQL Procedure Developer Assessment

Study Guide Prepared by Killexams.com IBM Dumps Experts

Exam Questions Updated On :



Killexams.com A2090-545 Dumps and Real Questions

100% Real Questions - Exam Pass Guarantee with High Marks - Just Memorize the Answers



A2090-545 exam Dumps Source : Assessment: DB2 9.7 SQL Procedure Developer Assessment

Test Code : A2090-545
Test Name : Assessment: DB2 9.7 SQL Procedure Developer Assessment
Vendor Name : IBM
: 115 Real Questions

Very easy way to pass A2090-545 exam with questions and examination Simulator.
I subscribed on killexams.com by the suggession of my friend, so as to get a few greater resource for my A2090-545 exams. As soon as I logged directly to this killexams.com I felt comfy and relieved considering the fact that I knew this can help me get through my A2090-545 test and that it did.


Worked hard on A2090-545 books, but everything was in the .
if you want to trade your future and make certain that happiness is your destiny, you want to work difficult. working hard alone isnt always sufficient to get to destiny, you want a few path with a purpose to lead you closer to the course. It became destiny that i found this killexams.com in the course of my test as it lead me towards my fate. My destiny turned into getting right grades and this killexams.com and its instructors made it feasible my teaching they so rightly that I couldnt in all likelihood fail by giving me the material for my A2090-545 exam.


It turned into extremely good to have real exam questions present day A2090-545 exam.
I prepared the A2090-545 exam with the help of killexams.com IBM test preparation material. it was complicated but overall very helpful in passing my A2090-545 exam.


real test A2090-545 Questions and answers.
killexams.com is straightforward and strong and you may pass the exam if you undergo their questions and answers. No phrases to explicit as i have passed the A2090-545 exam in first try. a few different questions banks also are availble inside the market, but I sense killexams.com is exceptional amongst them. i am very assured and am going to apply it for my different test additionally. thanks lots ..killexams.


I need dumps of A2090-545 examination.
i have advised about your gadgets to numerous partners and companions, and theyre all extraordinarily fulfilled. a great deal obliged killexams.com Questions & solutions for boosting up my career and helping me plan nicely for my excessiveexams. a whole lot preferred over again. I need to say that i am your greatest fan! I want you to recognize that I cleared my A2090-545 exam these days, delibemarks the A2090-545 route notes i purchased from you. I solved 86/95 questions within the exam. you are the quality training company.


Surprised to see A2090-545 latest questions in little price.
With most effective two weeks to move for my A2090-545 exam, I felt so helpless considering my terrible coaching. But, needed to pass the test badly as I desired to exchange my job. Finally, I located the question and solution by using killexams.com which removed my issues. The content of the manual changed into rich and specific. The simple and short answers helped make out the subjects effortlessly. Great manual, killexams. Also took assist from A2090-545 Official Cert Guide and it helped.


What is pass ratio of A2090-545 exam?
The crew in the back of killexams.com need to severely pat their returned for a activity well achieved! I have no doubts whilst pronouncing that with killexams.com, there is no threat which you dont get to be a A2090-545. virtually recommending it to the others and all of the top class for the future you guys! What a notable examine time has it been with the help for A2090-545 available at the internet site. You had been like a chum, a true buddy indeed.


It is really great experience to have A2090-545 dumps.
hello all, please be informed that i have passed the A2090-545 exam with killexams.com, which changed into my primary practisesource, with a stable average marks. that is a completely legitimate exam dump, which I noticeably advise to anybody opemarks towards their IT certification. that is a reliable way to prepare and pass your IT exams. In my IT organisation, there isnt a person who has no longer used/visible/heard/ of the killexams.com materials. not simplest do they assist you pass, but they make sure which you research and turn out to be a a success professional.


these A2090-545 present day dumps works exceptional inside the actual take a look at.
Hiya gents I passed my A2090-545 exam the usage of killexams.com brain dump test guide in most effective 20 days of preparation. The dumps virtually modified my life once I allotting them. Presently im labored in a first ratebusiness organisation with a decent income. Way to killexams.com and the entire organization of the trutrainers. Tough subjects are correctly secured by means of them. Likewise they provide remarkable reference it truly is useful for the examine motive. I solved nearly all questions in only 225 minutes.


it's miles right place to locate A2090-545 real test questions paper.
I took this exam final month and passed it way to my coaching with the killexams.com kit. This is a remarkable exam dump, greater dependable than I should count on. All questions are legitimate, and its also masses of coaching data. Better and more reliable than I predicted - I passed with over 97%, which is the satisfactory A2090-545 exam marks. I dont recognise why so few IT human beings understand about killexams.com, or perhaps its simply my conservative surroundings Anyways, I may be spreading the phrase amongst my friends due to the fact that this is super and may be useful to many.


IBM Assessment: DB2 9.7 SQL

IBM DB2 Will target Oracle Database developers

news

IBM DB2 Will target Oracle Database builders
  • through Jeffrey Schwartz
  • 04/24/09
  • IBM this week introduced a new version of its flagship DB2 database with utility to be able to supply compatibility with purposes developed for Oracle databases.

    Embedded in IBM's approaching DB2 9.7, code-named "Cobra" and slated for free up in June, is application that provides compatibility with EnterpriseDB Corp.'s Postgres Plus superior Server database, which supports all Oracle facts types, SQL syntax, semantics and different applications built with Oracle PL/SQL stored procedures. Postgres advanced Server is built on the open source PostgreSQL database.

    "The set of applied sciences we've got introduced will assist builders of customized purposes and packaged applications set up to DB2," noted Bernie Spang, director of method, analytics and records management for IBM's application group.

    For IBM, or not it's the primary time the company has licensed expertise from a provider of open source database utility, even though EnterpriseDB founder Andy Astor mentioned the application embedded in DB2 is in line with proprietary code.

    "this is now not an open supply play," Astor mentioned. "everything else they do is open source however that is one of the methods they make their funds, by selling that compatibility know-how both to vendors like IBM, during this case, and to clients in the variety of Postgres Plus superior Server."

    EnterpriseDB facets to a few shoppers including FTD and Sony online leisure which have migrated functions developed for Oracle databases to Postgres Plus. The take care of IBM "gives them with confirmed performance for enabling compatibility between DB2 and functions written for Oracle," noted 451 group analyst Matthew Aslett in an electronic mail.

    "Database migrations are advanced, time-ingesting, expensive and rare, so any functionality that makes the procedure less demanding is going to be welcomed by way of those companies that are organized to make the bounce," he delivered.

    The licensing pact is the first by using EnterpriseDB, but Astor mentioned others are being negotiated.

    IBM is hoping the circulation will motivate more shoppers to migrate to its InfoSphere Warehouse 9.7 business version, a knowledge-cleansing and company analytics platform according to DB2 and know-how IBM obtained from Cognos.

    the brand new release is focused at bringing data mining, analytics and cubing to departments of organisations and smaller corporations, Spang referred to. additionally new in DB2 is support for performing simultaneous transactions throughout XML and relational records.

    concerning the creator

    Jeffrey Schwartz is executive editor, points, for Redmond Developer news. you can contact him at jschwartz@reddevnews.com.


    CLPPLUS: IBM DB2's New Command Line Processor

    Rebecca Bond discusses a couple of of the options accessible in IBM DB2's new CLPPLUS command line processor that you can use to connect to databases and to define, edit, and run statements, scripts, and instructions.

    Even when it doesn’t involve security, I have fun with enjoying games and solving puzzles, notably if they involve DB2. today’s video game? What do they find out about DB2’s new CLPPLUS command line processor in two hours or less?

    here's a swap. I’m going to begin on the end of this contest after which walk you through the online game play to demonstrate you the winning strategy. if you’re one of those individuals who likes to study the closing chapter first, this text is for you.

    The Win at the conclude:

    The CLPPLUS command processor, which is a newer, more extreme DB2 CLP command line processor, behaves like I do when I even have handed my 18 cup a day espresso quota. If this thing changed into working in opposition t me in a half marathon, I’d should have an hour head birth to beat it to the conclude line. if you use the command line in any respect, there's tons to this game that allows you to guarantee a successful effect.

    The game starts:

    To begin the video game, I actually have installed DB2 9.7 commercial enterprise Server edition and upgraded to Fixpack 1 to take capabilities of some more recent facets. I don’t want to play solitaire with no full deck, and that i don’t like to be trained application that isn’t essentially the most existing.

    IBM’s web page says that the Command Line Processor Plus (CLPPlus) offers a command-line person interface for you to use to hook up with databases and to define, edit, and run statements, scripts, and commands. Sounds good to this point, but the place is the successful take advantage of (as a result of all games have some strategy factor, correct?). Wait, I don’t need to cheat. Don’t inform me if you know this one.

    I’ll beginning through ... smartly...“starting”. due to the fact I’m engaged on a windows platform these days, i will be able to just use the menu choice:

    Windows Menu Option

    Or, i can open a windows DOS command and type clpplus at the instantaneous. you probably have any challenge starting your CLPPLUS classes, seek advice from the troubleshooting counsel here: IBM DB2 Database for Linux, UNIX, and windows counsel center. Search on the subject “Troubleshooting CLPPlus issues”.

    Now I’ll connect to my sample database. there's multiple strategy to try this, however I’ll simply take the primary approach through typing connect and following the prompts for now:

    Type CONNECT and follow the prompts

    note something? here's a good game exploit. There became no catalog database commentary and on the grounds that i was the usage of the pattern database and the default port, I simplest had to enter my identification and password and that i changed into immediately related to the database. when I’m able to disconnect from the database, all it takes is a DISCONNECT keyword.

    If I wish to run an OS command, all I must do is precede it with the key phrase HOST. maybe I want to see what data are in my listing so i would classification:

    SQL> HOST dir

    To do the same thing on UNIX, I’d type

    SQL> host ls

    Now I need to be aware of extra concerning the alternatives, so I’ll ask for assist.

    Ask for help

    be aware that if I desire, i will be able to simply ask for support on a specific theme, or i will type assist INDEX and get the entire list. That’s a pleasant function except I get greater comfortable with the instructions. I hope I had that choice when i was first gaining knowledge of chess.

    Being a DBA, I’m curious about what DB2 assistance i will acquire from CLPPLUS. The listing is:

  • SQL> GET DATABASE CONFIGURATION
  • SQL> GET DATABASE manager CONFIGURATION
  • SQL> replace DATABASE CONFIGURATION
  • SQL> replace DATABASE manager CONFIGURATION
  • SQL> RESET DATABASE CONFIGURATION
  • SQL> RESET DATABASE manager CONFIGURATION
  • When i take advantage of these commands, I find that those which have “DEFERRED VALUES” equivalent to DBM CFG and DB CFG monitor those as well. The shortcuts that i am so used to (DBM CFG for Database manager Configuration) also work and the instructions aren’t case sensitive so “get dbm cfg” works too.

    From what I read within the documentation, there are some high-quality logging and tracing aspects. i wanted to log my customer session so i attempted:

    SQL> Set logmode clpplus

    once I entire logging and wanted to disable it, I entered:

    SQL> Set logmode none

    The clpplus.log file became in my current listing and contained some very targeted information about my recreation when the logmode was set. i will be able to see this being a very constructive characteristic for debugging purposes. besides the fact that children, what I in fact wanted was to log my CLPPLUS session from an enter/output standpoint. That function is obtainable as SPOOL. To switch on logging of my CLPPLUS session, I’ll type:

    SQL> SPOOL mycmds

    With this on and a named file parameter provided, CLPPLUS will log my existing session commands to a file in my current listing known as “mycmds”. it truly is precisely what i wanted.

    pondering environment the logmode made me feel about “SET”, so I did a aid SET and pulled returned a protracted list of options for SET, together with imperative descriptions. The SET CLPPLUS command works simplest at the session level, so as soon as I “SET”, the value only lasts temporarily whereas I’m in that specific session.

    some of the marketed advantages of using CLPPLUS makes it sound like a great strategic maneuver that DBAs can use to effectively format screen output. I’m now not a fan of getting to make use of CHAR or SUBSTR to parse my output. To are trying the formatting facets out, I’m going to create a table for testing purposes. To increase the online game play, I’m going to position a typo in my syntax in order that i will be able to verify out what occurs when i use the CLPPLUS EDIT option.

    SQL> create desk locksmith.textual content (col1 char(80), col2 char(80))" 2 / ERROR near line 1: (this didn’t parse appropriately, that double quote has to go) SQL0010N The string steady beginning with """ doesn't have an ending string delimiter. [The slash symbol (/) provides the completion indicator to the editor]

    but here is convenient adequate to repair.

    SQL> edit

    At this aspect, my default editor, NOTEPAD, pops up with the syntax so that i will edit it and remove the offending double quote and shop the file.

    my default editor pops up with the syntax

    I’ve fixed my typo and saved my file. Now, all I must is class RUN to create the desk.

    SQL > run

    I’ll load some facts for checking out purposes, but before I do, I’ll set AUTOCOMMIT on for this session (the default is off, aside from DDL statements that are immediately committed.).

    SQL> set autocommit on

    (whilst you have been in the kitchen getting popcorn, I went forward and took my turn. I inserted two rows into their locksmith.text table.)

    Now, I want to play with the formatting options.

    First, I’ll study WRAP. setting WRAP off seems to permit truncation of a line in my output:

    Setting WRAP off seems to allow truncation of a line in my output

    With WRAP on, I get

    With WRAP on

    superior, but I don’t just like the headings, so I’m going to add some more formatting.

    Add some formatting

    This looks first rate, however there are such a lot of extra options accessible. alas, all games at some factor need to end and they need to now declare the suit a win, lose or draw and flow on. I call a tie.

    but before they put all the online game items away to play one other day, let’s seem to be on the rule book to discover some video game play options they didn’t get to cowl. which you can seem to be forward to exploring these the next time you want to play the new online game I’ve now dubbed “CLPPLUS smart DBA challenge”

    some other great CLPPLUS online game chocolates are:

  • A buffer may also be used to keep scripts, script fragments, SQL statements, SQL PL statements, or PL/SQL statements for enhancing after which execution. text within the buffer may also be listed, printed, edited, or run as a batch script.
  • A complete set of processor commands can also be used to outline variables and strings that will also be stored in the buffer.
  • capability to shop buffers or buffer output to a file.
  • assist for executing system-defined routines.
  • considering the game is well-nigh accomplished and it’s time to go home, I could be kind to my database and issue the DISCONNECT command which closes my database connection, however keeps me resident in the CLPPLUS session. when I’m through with all my CLPPLUS video games and fun and before I head out to remedy some urgent protection puzzler, I’ll problem the stop command to shut out my CLPPLUS session unless the subsequent game.

    Oh, and earlier than their next suit, for extra guidance on the way to guarantee a successful video game, you may wish to take a look at the CLPPLUS video at YouTube .

    [And, a big Shout Out to my friend, Serge Rielau, IBM SQL Architect extraordinaire, for taking time from his very busy schedule to review this article. Read his excellent article on running Oracle applications on DB2].

    extra elements

    IBM Command Line Processor PlusIBM CLPPlus instructions

    » See All Articles by means of Columnist Rebecca Bond


    Canonical brings IBM DB2 to Linux cloud

    Canonical has launched a digital equipment for operating instances of IBM DB2 database utility, the business announced on Wednesday.

    The utility appliance will comprise a copy of IBM’s DB2 express-C, so that it will run on the company’s Linux-based server distribution, Ubuntu 10.04 long term support Server version.

    “A virtual cloud appliance inclusive of Ubuntu and DB2 express-C will enable purchasers to straight away and simply installation DB2 in both public and personal cloud situations,” said Neil Levine, Canonical vp of commercial services, in a statement.

    Public cloud carrier suppliers comparable to Amazon and Rackspace use the server edition of Ubuntu Linux as part of their choices. Canonical is additionally marketing its Ubuntu commercial enterprise Cloud, which comprises a edition of the open-supply Eucalyptus cloud software, as a product that organizations can use to build their own deepest clouds.

    IBM presents DB2 specific-C as a free primary edition of DB2 it really is geared to smaller business deployments. IBM has additionally licensed that DB2, version 9.7 can run on Ubuntu 10.04.

    Canonical is not by myself in providing a cloud-in a position version of DB2. Amazon additionally

    here">offers four versions of DB2 as part of its Amazon net capabilities platform. It additionally presents Oracle, MySQL, Microsoft SQL Server, PostSQL and Sybase IQ, on a metered groundwork.

    Joab Jackson covers enterprise utility and regularly occurring technology breaking news for The IDG information carrier. observe Joab on Twitter at @Joab_Jackson. Joab’s e mail tackle is Joab_Jackson@idg.com


    Whilst it is very hard task to choose reliable exam questions / answers resources regarding review, reputation and validity because people get ripoff due to choosing incorrect service. Killexams. com make it certain to provide its clients far better to their resources with respect to exam dumps update and validity. Most of other peoples ripoff report complaint clients come to us for the brain dumps and pass their exams enjoyably and easily. They never compromise on their review, reputation and quality because killexams review, killexams reputation and killexams client self confidence is important to all of us. Specially they manage killexams.com review, killexams.com reputation, killexams.com ripoff report complaint, killexams.com trust, killexams.com validity, killexams.com report and killexams.com scam. If perhaps you see any bogus report posted by their competitor with the name killexams ripoff report complaint internet, killexams.com ripoff report, killexams.com scam, killexams.com complaint or something like this, just keep in mind that there are always bad people damaging reputation of good services due to their benefits. There are a large number of satisfied customers that pass their exams using killexams.com brain dumps, killexams PDF questions, killexams practice questions, killexams exam simulator. Visit Killexams.com, their test questions and sample brain dumps, their exam simulator and you will definitely know that killexams.com is the best brain dumps site.

    Back to Braindumps Menu


    Killexams CLO-001 practice questions | Killexams GB0-320 questions and answers | Killexams P2170-033 braindumps | Killexams HP0-756 braindumps | Killexams P2090-045 exam questions | Killexams 6202-1 questions and answers | Killexams HP0-W01 examcollection | Killexams 000-438 brain dumps | Killexams 050-684 free pdf | Killexams C90-03A practice exam | Killexams JK0-019 free pdf | Killexams 000-891 questions answers | Killexams HP0-A25 cram | Killexams 920-534 mock exam | Killexams 920-334 study guide | Killexams 1D0-621 brain dumps | Killexams A2090-544 free pdf | Killexams 1Y0-A11 dumps | Killexams AP0-001 dump | Killexams 920-338 practice questions |


    People used these IBM dumps to get 100% marks
    killexams.com is a definitive arrangement hotspot for passing the IBM A2090-545 exam. They have painstakingly agreed and gathered real exam questions, which are refreshed with indistinguishable recurrence from real exam is refreshed, and checked on by industry specialists. Colossal Discount Coupon and Promo codes are offered for awesome discount.

    We have Tested and Approved A2090-545 Exams. killexams.com provides the foremost actual and most up-to-date IT braindumps that much contain all info focuses. With the guide of their A2090-545 exam dumps, you do not need to squander your chance on spending time on reference books and easily have to be compelled to spend 10-20 hours to ace their A2090-545 real Questions and Answers. Whats more, they have an approach to furnish you with PDF Version and Exam Simulator Version test Questions and Answers. For Exam Simulator Version dumps, Its offered to relinquish the candidates mimic the IBM A2090-545 exam in an exceedingly real scenario. killexams.com Discount Coupons and Promo Codes are as under; WC2017 : 60% Discount Coupon for all exams on website PROF17 : 10% Discount Coupon for Orders larger than $69 DEAL17 : 15% Discount Coupon for Orders larger than $99 SEPSPECIAL : 10% Special Discount Coupon for All Orders Click http://killexams.com/pass4sure/exam-detail/A2090-545

    Quality and Value for the A2090-545 Exam : killexams.com Practice Exams for IBM A2090-545 are composed to the most elevated norms of specialized precision, utilizing just confirmed topic specialists and distributed creators for improvement.

    100% Guarantee to Pass Your A2090-545 Exam : If you dont pass the IBM A2090-545 exam utilizing their killexams.com testing software and PDF, they will give you a FULL REFUND of your buying charge.

    Downloadable, Interactive A2090-545 Testing Software : Their IBM A2090-545 Preparation Material gives you all that you should take IBM A2090-545 exam. Subtle elements are looked into and created by IBM Certification Experts who are continually utilizing industry experience to deliver exact, and legitimate.

    - Comprehensive questions and answers about A2090-545 exam - A2090-545 exam questions joined by displays - Verified Answers by Experts and very nearly 100% right - A2090-545 exam questions updated on general premise - A2090-545 exam planning is in various decision questions (MCQs). - Tested by different circumstances previously distributing - Try free A2090-545 exam demo before you choose to get it in killexams.com

    killexams.com Huge Discount Coupons and Promo Codes are as under;
    WC2017 : 60% Discount Coupon for all exams on website
    PROF17 : 10% Discount Coupon for Orders greater than $69
    DEAL17 : 15% Discount Coupon for Orders greater than $99
    DECSPECIAL : 10% Special Discount Coupon for All Orders


    A2090-545 | A2090-545 | A2090-545 | A2090-545 | A2090-545 | A2090-545


    Killexams 350-025 real questions | Killexams 1Z0-971 exam prep | Killexams CPIM-MPR questions and answers | Killexams 600-210 real questions | Killexams 920-324 dump | Killexams NS0-501 test prep | Killexams 132-S-911 study guide | Killexams HP0-M25 VCE | Killexams 000-891 questions and answers | Killexams 920-262 free pdf | Killexams 000-R15 brain dumps | Killexams F50-533 free pdf | Killexams JN0-361 mock exam | Killexams 000-571 Practice Test | Killexams Hadoop-PR000007 real questions | Killexams PC0-006 braindumps | Killexams 000-M34 free pdf download | Killexams HP3-C35 Practice test | Killexams HP0-A21 bootcamp | Killexams A00-205 study guide |


    killexams.com huge List of Exam Braindumps

    View Complete list of Killexams.com Brain dumps


    Killexams HP0-449 test questions | Killexams 1Z0-515 exam prep | Killexams ECP-541 questions answers | Killexams 000-200 test prep | Killexams 351-001 VCE | Killexams 920-482 real questions | Killexams 000-M20 mock exam | Killexams P2090-076 questions and answers | Killexams DC0-260 real questions | Killexams 210-065 dumps | Killexams 1Z0-042 dump | Killexams 4A0-101 brain dumps | Killexams 920-804 practice exam | Killexams 050-v71x-CSESECURID pdf download | Killexams 650-331 questions and answers | Killexams DEA-41T1 braindumps | Killexams HP0-A100 braindumps | Killexams 1Z0-040 braindumps | Killexams 2M00001A practice test | Killexams M2010-720 test prep |


    Assessment: DB2 9.7 SQL Procedure Developer Assessment

    Pass 4 sure A2090-545 dumps | Killexams.com A2090-545 real questions | http://smresidences.com.ph/

    Finding Database Object Dependencies

    By Gregory A. Larsen

    If you need to make small changes to an existing database object, then you will need to do some impact assessment to determine what SQL Server Objects will be affected by your small change. So how do you identify those database objects that will be affected by a scheme change? Is there a simple way to identify the store, procedures, triggers, views, etc. that might be affected when you implement a database change? Some shops have the luxury of still having the original developer on site that retained the intimate knowledge of the original design. Although in this day when IT staff move to new jobs frequently most IT shops are not so lucky to have a resident expert. Possibly other shops have documentation that identifies object dependencies, but my guess is these shops are few and far between. For these reasons, you need to have a way to programmatically find objects that are affected by any minor schema changes you might make. This article will discuss a couple of approaches you might use to help with your impact analysis when you are contemplating a database schema change.

    Using the SYSCOMMENTS table

    The actual code for views, rules, defaults, triggers, CHECK constraints, DEFAULT constraints and stored procedures are stored in the syscomments table. The column TEXT in the syscomments table contains the actual code for all these objects. Knowing this allows you to write some simple T-SQL code that can scan the syscomments table looking for an actual table column name or database object name that you are planning on changing. By scanning the syscomments table, you will be able to narrow the focus of your impact analysis down to only those objects that might be affected by your change.

    Let me demonstrate how you might query the syscomments table to identify objects that are affected by changing a column in a database table. For this example let’s assume I am going to make a change to the column "pub_id" in the "titles" table stored in the "pubs" database. In this case, to identify all the objects that use the "pub_id" column all I need to do is run the following query:

    use pubs go select name from syscomments c join sysobjects o on c.id = o.id where TEXT like '%pub_id%' and TEXT like '%Titles%'

    This query looks for syscomments rows that contain both the string "pub_id" and "titles" in the same record. With this query, I find all syscomments records that contain both a reference to the table and column that I planned on changing. This query joins the syscomments and sysobjects table so that the object name can be displayed in the results. When I run this command against the pubs database, on my server, I find that the following four objects reference the "pub_id" column in the "titles" table in the pubs database: titleview, reptq1, reptq2, reptq3.

    This method of searching for a string of code in the TEXT column of the syscomments table can be used for more than just finding table names and column names that are used by an object. You can also use this method to find any string of code in any object. If I want to find all the objects in the Northwind database that perform a "DELETE" command then the following code could be run:

    select name from Northwind.dbo.syscomments c join Northwind.dbo.sysobjects o on c.id = o.id where TEXT like '%delete%'

    Using this method is an easy and quick way of searching for any object that contains a fragment of code.

    Using the sp_depends Stored Procedure

    SQL Sever maintains a database system table named sysdepends. SQL Server uses this table to store object dependencies. This table only contains information about objects that are dependent upon other objects. It does not contain detailed dependency information, such as which attributes of an object are referenced by other objects. Even so, you can use the information in the sysdepends table to help narrow down your impact analysis when you are making a schema change.

    To access the sysdepends table information SQL Server provides the sp_depends system stored procedure. Here is an example of how to use this stored procedure:

    USE pubs EXEC sp_depends 'Titles'

    When I run this statement against my pubs database the following information is returned:

    In the current database, the specified object is referenced by the following: name type -------------------------------------------- ---------------- dbo.reptq1 stored procedure dbo.reptq2 stored procedure dbo.reptq3 stored procedure dbo.titleview view

    As you can see, my example returns all objects that are dependent on the table name "Titles." In this case, there are three stored procedures and one view. This method of using the sp_depends system stored procedure identified the same set of objects as my custom T-SQL code above that queried the "syscomments" table.

    Not All Code is Stored in SQL Server

    Now keep in mind the syscomments and sp_depends methods may not find all your code that is affected by a schema change. These two methods are only able to scan objects that are stored in SQL Server (views, rules, defaults, triggers, CHECK constraints, DEFAULT constraints, and stored procedures). If you have written your application code in such a way that allows you to issue T-SQL SELECT, UPDATE, INSERT and DELETE statements in code blocks that are not stored in SQL Server, then you will need to use other methods for scanning this code. Hopefully where you store the code for your application outside SQL Server is in a repository or file structure of some kind where you can do a global find or search of some kind.

    Conclusion

    When you are making a database schema change, it is important to identify all the objects that might be affected by the change. Without doing a complete impact assessment, you run the risk of causing problems when implementing your schema change. Therefore having some easy, and automated method to help identify the objects that will be impacted by your schema change is critical, and a time saver. The next time you are doing some impact analysis associated with a schema change to your database, you might consider using one or all the methods I discussed to help narrow down the objects impacted by your proposed change.

    » See All Articles by Columnist Gregory A. Larsen


    The DB2 Security Plan

    This chapter is from the book 

    Because you’re reading this book, it’s obvious that you have an interest in protecting the data assets held by your organization; but unless you have a very small organization with a single very small database, you need a plan and a leader. Formalization of that plan will provide great assistance toward the goal of database security, and during the formulation of that plan, a leader (corporate sponsor) should emerge.

    Many organizations already have some type of security plan in place that may or may not include specifics on database protection. If your organization has an enterprise-level security plan, the DB2 database security plan should be a significant and highly visible part of that document. If you do not have an enterprise level security plan in place, the DB2 database security plan should still be created, even if it must be undertaken as a standalone document. Given the criticality of protecting the data stored in those DB2 databases, ignoring the security responsibilities may mean unacceptable organizational risk.

    The DB2 security plan document is a road map that will provide the foundation for the enforcement of the operational DB2 database security policies that will be implemented. It should provide a meeting of the minds with regard to the security of the organization databases and how DB2 should be used to fulfill those needs.

    If you are a DB2 database administrator, you have a vested interest in getting a DB2 security plan in writing. This plan, once written and approved by management, can be your guideline for setting up your database security policies. It can be referred to when questions arise, such as access levels, provided to auditors to facilitate their reviews, and should be reviewed or revised when new applications are installed. A major bonus for you is that when finalized, it will keep you from having to answer the same questions about security over and over again, saving you time and allowing preservation of your sanity.

    So, if necessary, take the lead on getting a committee involved in formalizing a written DB2 security plan. You may get resistance, but remember that the database you are trying to protect is potentially one of the greatest assets held in trust by your organization (and the people it serves), and, therefore, it should be treated and protected just as any other valuable corporate asset would.

    Security Plan Meeting Participants

    The first step toward achieving your database security plan is to identify the team members who should be involved in the creation and review of the document. In a typical organization, the positions listed in Table 2.1 might be considered key to this task.

    Table 2.1. Database Security Plan Team Members

    All Appropriate Members of Management

    CIO

    CTO

    Vice presidents

    Division managers

    Technical team leads

    Application subject matter experts (SMEs)

    Network administrators

    Systems administrators

    Database administrators (DBAs)

    Corporate security officers

    One important factor in determining who to include is the realization that the matters discussed in these meetings could be used by internal or external sources in inappropriate ways. Because the focus of the meetings is to discuss and mitigate current and future threats, the core meeting group should consist of individuals who are trusted by the organization to maintain the confidentiality of issues discussed.

    To succeed, the database security plan needs a corporate sponsor. This should be someone within the organization who has the appropriate level of interest, authority, and responsibility to approve, communicate, and enforce the resultant plan. If your corporation has a security officer, especially if the position that person holds has sufficient status within the organization, the security officer may be able to fulfill the sponsor role.

    Obviously in large organizations, division personnel should be involved in the process. It is important to get their unique perspective on database security because they may face challenges that are not easily identified. Depending on your organizational structure, division technical personnel should be invited to the meeting if they have discrete environments or can provide technical expertise relevant to database or application security.

    Communication and information from application SMEs will likely be necessary to determine the granularity of database security needed. These are the individuals who can indicate which data needs the most protection, which users should have access and the level of that access, and how to determine whether a breach has occurred. These individuals may be technical leads, application programmers, or just those who know the application well because of their role. For example, an accountant might be the individual who knows the most about the general ledger applications.

    The remaining participants should be the hands-on technical individuals who typically have the roles of network administrator, system administrator, and DBA. Participation of individuals who fulfill these roles is absolutely critical to successfully designing a plan because each will bring a unique focus to the process.

    Gather Information

    Before any meetings are scheduled, there should be some gathering and summarization of information if it is not already readily available to the team. A minimum starting list of items includes the following:

  • Current security documents
  • Standards (formal or informal)

    Any written security guidelines

    Any informal security policies that have been enforced in the past

  • Hardware in use or proposed for DB2 databases

    Machine specifics

    Physical location

  • Connectivity mechanisms in use
  • Current authentication methods
  • Operating system information

    OS type

    OS level

  • Maintenance procedures, such as patch management, currently in place
  • Licensing agreements
  • User and group information
  • List of DB2 instances by hardware

    The type of instance

  • Development
  • Test
  • Production
  • The database manager configuration parameters in use per instance (DBM configuration)
  • The product and level of the DB2 code base installed (DB2LEVEL command output)
  • List of DB2 databases by instance
  • Database configuration(s) (DB configuration)
  • Backup procedures including types, frequency, and storage location
  • Applications currently being run (as observed or proposed)
  • Typical number of users
  • Access control measures in place
  • List of applications
  • Type
  • Web-based
  • Third-party
  • Batch
  • Application “owners”
  • Prior risk assessments
  • Information on known data classification
  • Special security considerations
  • Federated
  • Information Integrator
  • High Availability Cluster Multi-Procesing (HACMP™)
  • Results and recommendations of any security audits that have been performed
  • Meeting Goals and Desired Outcomes

    After you get the appropriate parties and the initial information together, meeting goals should be established. It is difficult to create a database security plan in only one meeting unless your organization is very small, so it might be best to create overall goals and then plan enough meetings to allow time for discussion.

    Segment the discussion with the idea that internal and external security may pose different threats and, therefore, require a different set of goals. Uniformity of standards can simplify security policies and should be considered to the extent that they can be implemented across the organization without incurring increased risk.

    The result of these meetings should include a comprehensive, written policy addressing the components of the database security plan, including at a minimum, the following:

  • Appropriate analysis of internal and external database security risks and the current approach toward their mitigation
  • External security authorization mechanism
  • Group and user naming standards
  • Password standards and change guidelines
  • Operating system standards (for DB2 files, file systems, logical volumes)
  • A workable blueprint for setting up the DB2 database security policies

    What security standards should be set for all databases?

  • A List of access control levels by database
  • Who needs access and at what level?
  • Granularity of access control needed
  • What security access is needed by the following?
  • Application
  • Group
  • User
  • Database
  • Who will be responsible for internal user and/or group account setup?
  • How will user accounts be tracked?
  • How will terminations and revocations be handled?
  • How will necessary access changes be conveyed?
  • What approvals are needed?
  • What forms are required?
  • What sign-offs must be in place?
  • Identification and classification of extremely sensitive information
  • By database
  • By application
  • By table
  • By column
  • By row
  • Identification of overall DB2 security management responsibility
  • Who is the owner?
  • Who can delegate?
  • Who is the custodian?
  • Uniformity of database policies and any acceptable deviations
  • Auditing requirements
  • Incident handling procedures
  • The review cycle for the formulated database security plan
  • Are there regulatory requirements for the review cycle?
  • Should the entire plan be reviewed at once?
  • Will there be a review after hardware changes?
  • Will there be a review after software changes?
  • Meeting Facilitation Tools

    When considering an analysis of internal and external database security risks, you can use a grid approach. Table 2.2 shows a basic example.

    Table 2.2. Database Security Risk Grid Example

    Internal

    Threat

    Plan

    Shared passwords

    High

    New password policy.

    Disgruntled employees

    Medium

    Review access levels before personnel actions are undertaken.

    Users granted inappropriate access to data

    Medium

    Check and review current database grants; create an access control policy.

    External

    Threat

    Plan

    Introduction of vulnerabilities due to lack of maintenance

    High

    Schedule maintenance window for patches.

    Hacker attack

    High

    Keep current with patches; encrypt sensitive data; change passwords on a regular basis; enforce password standards; undertake vulnerability assessment.

    Web users with incorrect access

    High

    Check and review current database grants; create an access control policy.

    It is likely that this grid (or any other facilitation mechanism used to capture this detail) can grow quite large. It should be viewed as a brainstorming tool to assist in determining the scope of risk. As in the example, it is likely that the items under the Plan column may contain duplications that might occur when one risk can be mitigated by the same action as another risk. An example of this is a risk of external resources and internal resources holding inappropriate access to data. Whereas each presents a different threat to the database and potentially a different level of risk, one action that should be included in the plan for proposed mitigation of both is to review current database access levels and create an enforceable access control policy.

    The formation of this grid may assist in identifying the highest-priority items that should be addressed first (even before the plan is completed) and might lead to discovery of threats not currently on the corporate radar. As a first step before tackling the robust work of actually formulating the part of the plan that will serve as a blueprint for the DB2 database security policy, the grid will facilitate an assessment of where the corporation stands now with regard to database security and potentially assist in identification of any serious lapses.

    After the assessment of internal and external threats has been completed, the results should be summarized and organized to be used as input for the next steps.

    Determining the Authentication Method and User/Password Security

    Authentication for DB2 databases is handled by a security facility outside of DB2, such as the operating system, Kerberos, or other plug-in. Although it is not necessary at this point in the process to be specific as to the parameter settings (authentication is discussed in detail in Chapter 5, “Authorization—Authority and Privileges”), the overall authentication requirements should be documented. The discussion should include a determination as to where the authentication should take place (that is, client, server, DB2 connect server, host) and whether encryption (Chapter 7, “Encryption [Cryptography] in DB2”) is required.

    Standards should be developed for naming conventions for groups and users. Part of this strategy should be that known default or easily identifiable group names and usernames are not allowed. Some that are commonly used in many DB2 shops (and should therefore be avoided) include the following

  • db2admin
  • db2as
  • db2inst1
  • db2fenc1
  • Another important discussion regarding groups revolves around the DB2 group known as PUBLIC. DB2 comes with this group by default. This group can (and should) be locked down unless there is some documented reason for this group to maintain certain low-level privileges. Prior to DB2 9, this group always received a number of privileges from the moment the database was created. With DB2 9, an alternative for dealing with the PUBLIC group privileges is made available. When creating a new database, adding the keyword RESTRICTIVE changes the default behavior, and no privileges are automatically granted to the PUBLIC group. If this keyword is not used, the following permissions are available to the PUBLIC group after the database has been created:

  • CREATETAB
  • BINDADD
  • CONNECT
  • IMPLSCHEMA
  • EXECUTE with GRANT on all procedures in schema SQLJ
  • EXECUTE with GRANT on all functions and procedures in schema SYSPROC
  • BIND on all packages created in the NULLID schema
  • EXECUTE on all packages created in the NULLID schema
  • CREATEIN on schema SQLJ
  • CREATEIN on schema NULLID
  • USE on table space USERSPACE1
  • SELECT access to the SYSIBM catalog tables
  • SELECT access to the SYSCAT catalog views
  • SELECT access to the SYSSTAT catalog views
  • UPDATE access to the SYSSTAT catalog views
  • As you can see, the privileges for the PUBLIC group on a newly created database are significant. If discussions yield no valid reason for PUBLIC privileges, the RESTRICTIVE clause should be used for newly created databases.

    Excellent password security is one of the elements of a strong security plan. In addition to identifying the responsibility for password security enforcement and the mechanisms for change, the following topics should be considered:

  • Changes

    Will users change their own passwords?

    If not, how will they be notified of the changes?

  • Length of time between required resets/changes

    If not reset/changed, how long until lockout of the account?

    How many cycles must be completed before passwords can be reused?

  • Resets

  • Who will hold responsibility for password resets?
  • Will a secondary authentication be used to allow the user to reset it?
  • Secondary authentication question answered correctly
  • Biometric authentication
  • Electronic device such as a key card
  • Lockouts

    How many password attempts before lockout?

    What forms and approvals are to be in place?

    Who will have the authority to retrieve a lost password or credential?

  • In considering passwords, a discussion of composition standards for those passwords is necessary. The human factor in password issues is well recognized. If your users are allowed to create their own passwords, without any applicable standards, the strength of those passwords will be suspect. If complex passwords that are not easy to remember are instead assigned to users, in variably they will be written down someplace, and that overrides the strength of the complex password.

    One approach to this is to create a security password template. This provides the mechanism to ensure that the password meets certain standards, such as three capital letters, two numbers, one symbol, and two lowercase letters. However, this can be problematic. Consider that internal employees will know the template. This information could provide an unintended assist if an internal or former employee wanted to gain access through password hacking.

    It is critical that forbidden passwords include usernames, employee IDs, dictionary words (in any language), and true words with numeric replacements of ones or zeros. All these are easily hacked by a brute-force approach.

    It’s easy to say that passwords should never be shared, but harder to enforce that standard unless there is some strength behind the security plan, policies, and procedures that can bring consequences to those who violate this essential security foundation. Passwords should also be expired, but this brings up the question “When?” Too-frequent password expiration is problematic because users are tempted to write them down somewhere. A longer time between password changes means a longer exposure period. Changing passwords on a regular interval can be beneficial, but if this actual interval is widely known, this, too, can be a risk. Would you really want a hacker to know that passwords expire on the first Saturday of every other month? Encryption of all passwords is a strong recommendation. DB2 provides easily implemented encryption security features to protect passwords (and more), as discussed in Chapter 7.

    As with all security features, knowledge of current industry standards and practices regarding password topics will provide the best guidance. As security evolves, so do the attempts to thwart that security, so keeping current through a proactive approach is wise.

    Discussing the Blueprint

    Now that you have summarized the results of the internal and external risk assessment for database security and addressed authentication, it is time to begin work on the part of the plan that will eventually be used as a foundation for creating the actual DB2 database security policies. At this point, the team needs to have a good understanding of the internal and external threats that should be addressed to protect the database.

    During this phase of the meeting process, the team should begin to discuss the security standards for all corporate DB2 databases. Depending on the structure, complexity, and size of the organization, this can be intricate with multiple considerations per division, per database, per machine, and so on. The goal of this part of the plan is to integrate information discovered in previous steps to facilitate creation of the DB2 database security policies.

    At this phase of the process, the team should begin to discuss a workable set of security standards and how they should be applied.

    Questions to be answered and topics to be codified in the plan include the following:

  • The ability to uniformly apply database security standards

    Are there needs for differing standards based upon ...?

  • Divisions
  • OS types and levels
  • File system storage versus raw devices
  • Firewall
  • VPN
  • Federated
  • Replication
  • LDAP
  • Are there special considerations for specific third-party applications?

    If so, how will these differences be identified and handled?

  • Access control plan

    A statement identifying responsibility and ownership

  • Account/group setup
  • Account tracking
  • Terminations
  • Changes
  • Matrixes of access levels needed (see Table 2.3)

  • For authorities
  • For privileges
  • Special
  • Identification of necessary maintenance steps

  • Approvals
  • Forms
  • Sign-offs
  • Identification of extremely sensitive information for special consideration
  • Table 2.3. Database Authorities Matrix

    Instance Level

    Corporate Tech Arch Group

    Corporate DBAs

    Division 1 Tech Group

    Division 1 DBA Team Lead

    Conversion Team

    SYSADM

    Y

    N

    N

    N

    N

    SYSCTRL

    N

    Y

    N

    Y

    N

    SYSMAINT

    N

    Y

    Y

    Y

    N

    SYSMON

    N

    N

    N

    Y

    N

    Database Level

    Corporate Tech Arch Group

    Corporate DBAs

    Division 1 Tech Group

    Division 1 DBA Team Lead

    Conversion Team

    DBADM

    N

    Y

    Y

    Y

    N

    LOAD (with insert privilege)

    N

    N

    N

    N

    Y

    As mentioned previously, matrixes can aid in identifying access requirements. You can then use these matrixes as input for the DB2 database security policies. The examples in Tables 2.3 and 2.4 consider specific access levels and decode, by group, the level(s) that should be applied. Although the examples represent true discussion points, the values assigned here are for a fictional company, and no special meaning should be ascribed to them.

    Table 2.4. Database Privileges Matrix

    Public

    All Groups

    Software Development

    Conversion Group

    ETL Group

    Connect to database

    N

    Y

    Y

    Y

    Y

    Create new packages

    N

    N

    Y

    Y

    Y

    Create tables

    N

    N

    N

    Y

    N

    Unfenced stored procedures or UDFs

    N

    N

    N

    N

    Y

    Implicitly define schemas

    N

    N

    N

    N

    N

    Connect to database that is in quiesce state

    N

    N

    N

    N

    Y

    Allow user to create a procedure for use by other applications or users

    N

    N

    Y

    Y

    Y

    Definitions and detailed explanations of authorities and privileges are covered in Chapter 5.

    You can build similar matrixes to assist in identification of the additional security privileges to be addressed in the DB2 database security policies. These could include the following:

  • Schema

    Create objects within the schema

    Alter objects within the schema

    Drop objects within the schema

  • Tablespace

    Create tables in a specific tablespace

  • Tables and views

    Control of a table or view

    Add columns

    Add or change comments

    Add a primary key

    Add a unique constraint

    Create or drop a table check constraint

    Select, insert, update, and/or delete rows

    Create indexes

    Export

    Create and drop foreign keys

  • Packages

    Rebind

    Execute

    Allow package privileges for others

  • Drop and control on indexes
  • Execute routines
  • Use and alter on sequences
  • Passthru (in a Federated database environment)
  • Next Steps

    Now that the plan has been visualized, it is time to put it to paper. In thinking about what has been covered, it should be obvious that some of the information provided in this living document could be sensitive in nature. It is detailing how your corporation plans to mitigate security risks for the database and, therefore, could provide information to hackers or an internal employee and become an unintended aid for the very risks the plan is meant to address.

    Think about the “security” of the database security plan document. It should be protected while it is being written. Leaving parts of it lying around while it is being typed is not appropriate. The persons doing the typing should be trusted employees, too. At a minimum, the electronic copies of the plan should be password protected.

    Because of the sensitivity of the information, it is best to disseminate the plan on an “as needed” basis. One possible scenario is to create a security library with all security documentation provided through a signed checkout process. Other steps such as limiting the use of the document to one room that does not have a copier and stamping each page with a highly specific watermark to verify authenticity could provide some measure of further security.


    Allow Repeated Change With SQL Triggers

    In my previous article, I described how an RPG program as a before trigger, could be used to synchronize two columns in a table. There is an alternative. Instead of using an RPG trigger, they could have used an SQL trigger. An SQL trigger allows us to be more specific about when the trigger is activated and also has the advantage of portability to other database management systems.

    The scenario was where a date, stored as a packed numeric column, was to be converted to a proper date field. This was accomplished by duplicating the packed numeric column (and its contents) as a date column, recompiling all programs that accessed the table, and adding a before trigger to the table for insert and update. The trigger ensured that the contents of the packed numeric column and the date column were kept in synch.

    The outcome was that programs could then be changed on a one-by-one basis and be put into production. The trigger program would keep the dates in synch.

    Even though the RPG trigger program works perfectly and meets all the requirements, there is an overhead in that the trigger program is called before a row is changed, regardless of whether or not one of the date fields has changed.

    With SQL triggers they have the ability to specify triggers at the column level. So let’s have a look at how they would achieve the same outcome using SQL triggers.

    We will need to define three SQL triggers:

  • A trigger to run before a row is inserted.
  • A trigger to run before the packed numeric column (containing a date) is changed.
  • A trigger to run before the date column is changed.
  • But First

    Since there is some code that will be duplicated between the triggers, they will write a couple of stored procedures. Doing so will save us having to copy and paste some code.

    The first snippet below shows the code needed to create the stored procedure SP_DATE_TO_NUMBER. This procedure takes a date field (in ISO format) and converts it to a numeric field (in ISO format).

    CREATE PROCEDURE SP_DATE_TO_NUMBER ( IN DATEIN DATE , OUT DATEOUT DECIMAL(8, 0) ) LANGUAGE SQL SPECIFIC SP_DATE_TO_NUMBER DETERMINISTIC CONTAINS SQL CALLED ON NULL INPUT PROGRAM TYPE SUB COMMIT ON RETURN YES SET OPTION ALWBLK = *ALLREAD , ALWCPYDTA = *OPTIMIZE , COMMIT = *NONE , DECRESULT = (31, 31, 00) , DFTRDBCOL = *NONE , DYNDFTCOL = *NO , DYNUSRPRF = *USER , SRTSEQ = *HEX BEGIN NOT ATOMIC DECLARE CHARDATE CHAR ( 10 ) ; SET CHARDATE = CHAR(DATEIN) ; SET DATEOUT = INT(SUBSTR(CHARDATE, 1, 4) || SUBSTR(CHARDATE, 6, 2) || SUBSTR(CHARDATE, 9, 2)) ; END ;

    Next they will look at the code used to create the stored procedure SP_NUMBER_TO_DATE. This procedure takes a numeric field (in ISO format) and converts it to a date field (in ISO format).

    CREATE PROCEDURE SP_NUMBER_TO_DATE ( IN DATEIN DECIMAL(8, 0) , OUT DATEOUT DATE ) LANGUAGE SQL SPECIFIC SP_NUMBER_TO_DATE DETERMINISTIC CONTAINS SQL CALLED ON NULL INPUT PROGRAM TYPE SUB COMMIT ON RETURN YES SET OPTION ALWBLK = *ALLREAD , ALWCPYDTA = *OPTIMIZE , COMMIT = *NONE , DECRESULT = (31, 31, 00) , DFTRDBCOL = *NONE , DYNDFTCOL = *NO , DYNUSRPRF = *USER , SRTSEQ = *HEX BEGIN NOT ATOMIC DECLARE CHARDATE CHAR ( 8 ) ; SET CHARDATE = CHAR ( DATEIN ) ; SET DATEOUT = DATE (SUBSTR(CHARDATE, 1, 4) || '-' || SUBSTR(CHARDATE, 5, 2) || '-' || SUBSTR(CHARDATE, 7, 2)) ; END ;

    When The Numeric Column Changes

    The next piece of code they will examine is used to create the SQL trigger TRG_SQL_NUMBER_TO_DATE. This trigger is called before a change is made to the DATESOLD column. The important points to note are:

  • When the trigger is to be called “before update of DATESOLD”, this means that the trigger will only be called if the value of DATESOLD changes and not if the value of any other column changes.
  • To identify column names in the new image, referencing NEW as NEW_ROW, means that all columns in the new image are qualified with NEW_ROW.
  • The trigger itself simply calls the SP_NUMBER_TO_DATE stored procedure to perform the conversion of the date in the numeric column to the date column, i.e., the two columns being passed as parameters.
  • There is no requirement to specify Allow Repeated Change. With SQL triggers, columns in the NEW row buffer may be changed using a SET operation or, as in this example, using an OUT or INOUT parameter on a stored procedure.
  • Here’s the code used to create the stored Procedure TRG_SQL_NUMBER_TO_DATE.

    CREATE TRIGGER TRG_SQL_NUMBER_TO_DATE BEFORE UPDATE OF DATESOLD ON PRODUCT REFERENCING NEW AS NEW_ROW FOR EACH ROW MODE DB2ROW SET OPTION ALWBLK = *ALLREAD , ALWCPYDTA = *OPTIMIZE , COMMIT = *NONE , DECRESULT = (31, 31, 00) , DFTRDBCOL = *NONE , DYNDFTCOL = *NO , DYNUSRPRF = *USER , SRTSEQ = *HEX BEGIN ATOMIC CALL SP_NUMBER_TO_DATE(NEW_ROW.DATESOLD, NEW_ROW.LAST_SELL) ; END ;

    When The Date Column Changes

    Next, let’s look at the code used to create the SQL trigger TRG_SQL_DATE_TO_NUMBER. This trigger is called before a change is made to the LAST_SELL column. The process is very similar to the TRG_SQL_NUMBER_TO_DATE trigger, except that the SP_DATE_TO_NUMBER stored procedure is called to convert the date from the date column to the numeric column.

    CREATE TRIGGER TRG_SQL_DATE_TO_NUMBER BEFORE UPDATE OF LAST_SELL ON PRODUCT REFERENCING NEW AS NEW_ROW FOR EACH ROW MODE DB2ROW SET OPTION ALWBLK = *ALLREAD , ALWCPYDTA = *OPTIMIZE , COMMIT = *NONE , DECRESULT = (31, 31, 00) , DFTRDBCOL = *NONE , DYNDFTCOL = *NO , DYNUSRPRF = *USER , SRTSEQ = *HEX BEGIN ATOMIC CALL SP_DATE_TO_NUMBER(NEW_ROW.LAST_SELL, NEW_ROW.DATESOLD) ; END ;

    When A Row Is Inserted

    Finally, the last piece of code is used to create the SQL trigger TRG_SQL_SYNC_NEW_DATES. This trigger is called before a new row is inserted. Based on whether or not the value of the numeric column is zero, the trigger will call either the SP_NUMBER_TO_DATE stored procedure (to perform the conversion of the date in the numeric column to the date column), or the SP_DATE_TO_NUMBER stored procedure (to convert the date from the date column to the numeric column).

    CREATE TRIGGER TRG_SQL_SYNC_NEW_DATES BEFORE INSERT ON PRODUCT REFERENCING NEW AS NEW_ROW FOR EACH ROW MODE DB2ROW SET OPTION ALWBLK = *ALLREAD , ALWCPYDTA = *OPTIMIZE , COMMIT = *NONE , DECRESULT = (31, 31, 00) , DFTRDBCOL = *NONE , DYNDFTCOL = *NO , DYNUSRPRF = *USER , SRTSEQ = *HEX BEGIN ATOMIC IF NEW_ROW . DATE_LAST_SOLD = 0 THEN CALL SP_DATE_TO_NUMBER(NEW_ROW.LAST_SELL, NEW_ROW.DATESOLD) ; ELSE CALL SP_NUMBER_TO_DATE(NEW_ROW.DATESOLD, NEW_ROW.LAST_SELL) ; END IF ; END ;

    RPG Triggers Vs. SQL Triggers

    Both RPG and SQL triggers have their advantages.

    With the RPG trigger they only had one trigger program that looked after all of the processing. The only problem was the trigger was activated even when one of the columns they were concerned about was not changed.

    With the SQL triggers, they could be more specific about when the triggers are activated but they had to write separate trigger programs.

    Either approach is valid. Why not try them both?

    Paul Tuohy is CEO of ComCon, an iSeries consulting company, and is one of the co-founders of System i Developer, which hosts the RPG & DB2 Summit conferences. He is an award-winning speaker who also speaks regularly at COMMON conferences, and is the author of “Re-engineering RPG Legacy Applications,” “The Programmers Guide to iSeries Navigator,” and the self-study course called “iSeries Navigator for Programmers.” Send your questions or comments for Paul to Ted Holt via the IT Jungle Contact page.

    RELATED STORY

    Allow Repeated Change With Before Triggers

                         Post this story to del.icio.us               Post this story to Digg    Post this story to Slashdot



    Direct Download of over 5500 Certification Exams

    3COM [8 Certification Exam(s) ]
    AccessData [1 Certification Exam(s) ]
    ACFE [1 Certification Exam(s) ]
    ACI [3 Certification Exam(s) ]
    Acme-Packet [1 Certification Exam(s) ]
    ACSM [4 Certification Exam(s) ]
    ACT [1 Certification Exam(s) ]
    Admission-Tests [13 Certification Exam(s) ]
    ADOBE [93 Certification Exam(s) ]
    AFP [1 Certification Exam(s) ]
    AICPA [2 Certification Exam(s) ]
    AIIM [1 Certification Exam(s) ]
    Alcatel-Lucent [13 Certification Exam(s) ]
    Alfresco [1 Certification Exam(s) ]
    Altiris [3 Certification Exam(s) ]
    Amazon [2 Certification Exam(s) ]
    American-College [2 Certification Exam(s) ]
    Android [4 Certification Exam(s) ]
    APA [1 Certification Exam(s) ]
    APC [2 Certification Exam(s) ]
    APICS [2 Certification Exam(s) ]
    Apple [69 Certification Exam(s) ]
    AppSense [1 Certification Exam(s) ]
    APTUSC [1 Certification Exam(s) ]
    Arizona-Education [1 Certification Exam(s) ]
    ARM [1 Certification Exam(s) ]
    Aruba [8 Certification Exam(s) ]
    ASIS [2 Certification Exam(s) ]
    ASQ [3 Certification Exam(s) ]
    ASTQB [8 Certification Exam(s) ]
    Autodesk [2 Certification Exam(s) ]
    Avaya [101 Certification Exam(s) ]
    AXELOS [1 Certification Exam(s) ]
    Axis [1 Certification Exam(s) ]
    Banking [1 Certification Exam(s) ]
    BEA [5 Certification Exam(s) ]
    BICSI [2 Certification Exam(s) ]
    BlackBerry [17 Certification Exam(s) ]
    BlueCoat [2 Certification Exam(s) ]
    Brocade [4 Certification Exam(s) ]
    Business-Objects [11 Certification Exam(s) ]
    Business-Tests [4 Certification Exam(s) ]
    CA-Technologies [20 Certification Exam(s) ]
    Certification-Board [10 Certification Exam(s) ]
    Certiport [3 Certification Exam(s) ]
    CheckPoint [43 Certification Exam(s) ]
    CIDQ [1 Certification Exam(s) ]
    CIPS [4 Certification Exam(s) ]
    Cisco [319 Certification Exam(s) ]
    Citrix [48 Certification Exam(s) ]
    CIW [18 Certification Exam(s) ]
    Cloudera [10 Certification Exam(s) ]
    Cognos [19 Certification Exam(s) ]
    College-Board [2 Certification Exam(s) ]
    CompTIA [76 Certification Exam(s) ]
    ComputerAssociates [6 Certification Exam(s) ]
    Consultant [2 Certification Exam(s) ]
    Counselor [4 Certification Exam(s) ]
    CPP-Institute [4 Certification Exam(s) ]
    CSP [1 Certification Exam(s) ]
    CWNA [1 Certification Exam(s) ]
    CWNP [13 Certification Exam(s) ]
    CyberArk [1 Certification Exam(s) ]
    Dassault [2 Certification Exam(s) ]
    DELL [11 Certification Exam(s) ]
    DMI [1 Certification Exam(s) ]
    DRI [1 Certification Exam(s) ]
    ECCouncil [22 Certification Exam(s) ]
    ECDL [1 Certification Exam(s) ]
    EMC [128 Certification Exam(s) ]
    Enterasys [13 Certification Exam(s) ]
    Ericsson [5 Certification Exam(s) ]
    ESPA [1 Certification Exam(s) ]
    Esri [2 Certification Exam(s) ]
    ExamExpress [15 Certification Exam(s) ]
    Exin [40 Certification Exam(s) ]
    ExtremeNetworks [3 Certification Exam(s) ]
    F5-Networks [20 Certification Exam(s) ]
    FCTC [2 Certification Exam(s) ]
    Filemaker [9 Certification Exam(s) ]
    Financial [36 Certification Exam(s) ]
    Food [4 Certification Exam(s) ]
    Fortinet [14 Certification Exam(s) ]
    Foundry [6 Certification Exam(s) ]
    FSMTB [1 Certification Exam(s) ]
    Fujitsu [2 Certification Exam(s) ]
    GAQM [9 Certification Exam(s) ]
    Genesys [4 Certification Exam(s) ]
    GIAC [15 Certification Exam(s) ]
    Google [4 Certification Exam(s) ]
    GuidanceSoftware [2 Certification Exam(s) ]
    H3C [1 Certification Exam(s) ]
    HDI [9 Certification Exam(s) ]
    Healthcare [3 Certification Exam(s) ]
    HIPAA [2 Certification Exam(s) ]
    Hitachi [30 Certification Exam(s) ]
    Hortonworks [4 Certification Exam(s) ]
    Hospitality [2 Certification Exam(s) ]
    HP [752 Certification Exam(s) ]
    HR [4 Certification Exam(s) ]
    HRCI [1 Certification Exam(s) ]
    Huawei [21 Certification Exam(s) ]
    Hyperion [10 Certification Exam(s) ]
    IAAP [1 Certification Exam(s) ]
    IAHCSMM [1 Certification Exam(s) ]
    IBM [1533 Certification Exam(s) ]
    IBQH [1 Certification Exam(s) ]
    ICAI [1 Certification Exam(s) ]
    ICDL [6 Certification Exam(s) ]
    IEEE [1 Certification Exam(s) ]
    IELTS [1 Certification Exam(s) ]
    IFPUG [1 Certification Exam(s) ]
    IIA [3 Certification Exam(s) ]
    IIBA [2 Certification Exam(s) ]
    IISFA [1 Certification Exam(s) ]
    Intel [2 Certification Exam(s) ]
    IQN [1 Certification Exam(s) ]
    IRS [1 Certification Exam(s) ]
    ISA [1 Certification Exam(s) ]
    ISACA [4 Certification Exam(s) ]
    ISC2 [6 Certification Exam(s) ]
    ISEB [24 Certification Exam(s) ]
    Isilon [4 Certification Exam(s) ]
    ISM [6 Certification Exam(s) ]
    iSQI [7 Certification Exam(s) ]
    ITEC [1 Certification Exam(s) ]
    Juniper [65 Certification Exam(s) ]
    LEED [1 Certification Exam(s) ]
    Legato [5 Certification Exam(s) ]
    Liferay [1 Certification Exam(s) ]
    Logical-Operations [1 Certification Exam(s) ]
    Lotus [66 Certification Exam(s) ]
    LPI [24 Certification Exam(s) ]
    LSI [3 Certification Exam(s) ]
    Magento [3 Certification Exam(s) ]
    Maintenance [2 Certification Exam(s) ]
    McAfee [8 Certification Exam(s) ]
    McData [3 Certification Exam(s) ]
    Medical [68 Certification Exam(s) ]
    Microsoft [375 Certification Exam(s) ]
    Mile2 [3 Certification Exam(s) ]
    Military [1 Certification Exam(s) ]
    Misc [1 Certification Exam(s) ]
    Motorola [7 Certification Exam(s) ]
    mySQL [4 Certification Exam(s) ]
    NBSTSA [1 Certification Exam(s) ]
    NCEES [2 Certification Exam(s) ]
    NCIDQ [1 Certification Exam(s) ]
    NCLEX [3 Certification Exam(s) ]
    Network-General [12 Certification Exam(s) ]
    NetworkAppliance [39 Certification Exam(s) ]
    NI [1 Certification Exam(s) ]
    NIELIT [1 Certification Exam(s) ]
    Nokia [6 Certification Exam(s) ]
    Nortel [130 Certification Exam(s) ]
    Novell [37 Certification Exam(s) ]
    OMG [10 Certification Exam(s) ]
    Oracle [282 Certification Exam(s) ]
    P&C [2 Certification Exam(s) ]
    Palo-Alto [4 Certification Exam(s) ]
    PARCC [1 Certification Exam(s) ]
    PayPal [1 Certification Exam(s) ]
    Pegasystems [12 Certification Exam(s) ]
    PEOPLECERT [4 Certification Exam(s) ]
    PMI [15 Certification Exam(s) ]
    Polycom [2 Certification Exam(s) ]
    PostgreSQL-CE [1 Certification Exam(s) ]
    Prince2 [6 Certification Exam(s) ]
    PRMIA [1 Certification Exam(s) ]
    PsychCorp [1 Certification Exam(s) ]
    PTCB [2 Certification Exam(s) ]
    QAI [1 Certification Exam(s) ]
    QlikView [1 Certification Exam(s) ]
    Quality-Assurance [7 Certification Exam(s) ]
    RACC [1 Certification Exam(s) ]
    Real Estate [1 Certification Exam(s) ]
    Real-Estate [1 Certification Exam(s) ]
    RedHat [8 Certification Exam(s) ]
    RES [5 Certification Exam(s) ]
    Riverbed [8 Certification Exam(s) ]
    RSA [15 Certification Exam(s) ]
    Sair [8 Certification Exam(s) ]
    Salesforce [5 Certification Exam(s) ]
    SANS [1 Certification Exam(s) ]
    SAP [98 Certification Exam(s) ]
    SASInstitute [15 Certification Exam(s) ]
    SAT [1 Certification Exam(s) ]
    SCO [10 Certification Exam(s) ]
    SCP [6 Certification Exam(s) ]
    SDI [3 Certification Exam(s) ]
    See-Beyond [1 Certification Exam(s) ]
    Siemens [1 Certification Exam(s) ]
    Snia [7 Certification Exam(s) ]
    SOA [15 Certification Exam(s) ]
    Social-Work-Board [4 Certification Exam(s) ]
    SpringSource [1 Certification Exam(s) ]
    SUN [63 Certification Exam(s) ]
    SUSE [1 Certification Exam(s) ]
    Sybase [17 Certification Exam(s) ]
    Symantec [135 Certification Exam(s) ]
    Teacher-Certification [4 Certification Exam(s) ]
    The-Open-Group [8 Certification Exam(s) ]
    TIA [3 Certification Exam(s) ]
    Tibco [18 Certification Exam(s) ]
    Trainers [3 Certification Exam(s) ]
    Trend [1 Certification Exam(s) ]
    TruSecure [1 Certification Exam(s) ]
    USMLE [1 Certification Exam(s) ]
    VCE [6 Certification Exam(s) ]
    Veeam [2 Certification Exam(s) ]
    Veritas [33 Certification Exam(s) ]
    Vmware [58 Certification Exam(s) ]
    Wonderlic [2 Certification Exam(s) ]
    Worldatwork [2 Certification Exam(s) ]
    XML-Master [3 Certification Exam(s) ]
    Zend [6 Certification Exam(s) ]





    References :


    Dropmark : http://killexams.dropmark.com/367904/11970544
    Dropmark-Text : http://killexams.dropmark.com/367904/12907999
    Blogspot : http://killexamsbraindump.blogspot.com/2017/12/look-at-these-a2090-545-real-question.html
    Wordpress : https://wp.me/p7SJ6L-2oH
    Box.net : https://app.box.com/s/k446sopp7dpfsfe1bqxeg2vujs2znps6






    Back to Main Page





    Killexams exams | Killexams certification | Pass4Sure questions and answers | Pass4sure | pass-guaratee | best test preparation | best training guides | examcollection | killexams | killexams review | killexams legit | kill example | kill example journalism | kill exams reviews | kill exam ripoff report | review | review quizlet | review login | review archives | review sheet | legitimate | legit | legitimacy | legitimation | legit check | legitimate program | legitimize | legitimate business | legitimate definition | legit site | legit online banking | legit website | legitimacy definition | pass 4 sure | pass for sure | p4s | pass4sure certification | pass4sure exam | IT certification | IT Exam | certification material provider | pass4sure login | pass4sure exams | pass4sure reviews | pass4sure aws | pass4sure security | pass4sure cisco | pass4sure coupon | pass4sure dumps | pass4sure cissp | pass4sure braindumps | pass4sure test | pass4sure torrent | pass4sure download | pass4surekey | pass4sure cap | pass4sure free | examsoft | examsoft login | exams | exams free | examsolutions | exams4pilots | examsoft download | exams questions | examslocal | exams practice |

    www.pass4surez.com | www.killcerts.com | www.search4exams.com | http://smresidences.com.ph/