Hot answers tagged

7

I don't know about iSeries7 but some DBMS (like Oracle) allow this - as long as t2 (b) is a unique or primary key: UPDATE ( SELECT t1.a AS t1a, t2.a AS t2a FROM t1 INNER JOIN t2 ON t2.b = t1.b ) u SET t1a = t2a ; Tested at (Oracle 11g) SQL-Fiddle Other DBMS allow this syntax (like SQL-Server which doesn't make any fuss about the ...


6

Try using the MERGE statement from SQL2003 Standard - which is the equivalent of UPDATE..SET..FROM syntax. Example: MERGE INTO dbo.t1 AS Target USING (SELECT a,b,c FROM dbo.t2) AS Source ON (Target.b = Source.b) WHEN MATCHED THEN UPDATE SET Target.a = Source.a Note: for iSeries, MERGE requires INTO (credit: Jeff Stevens)


5

Add WITH REPLACE to the end of your DECLARE GLOBAL TEMPORARY TABLE statement. This will create your table in QTEMP regardless of whether one already exists or not.


5

There is an IMPLICITLY HIDDEN column attribute that can be applied. Please check for this... This means the column will only appear when it is explicitly specified on the SELECT statement. Check the CREATE TABLE statement for this property, or materialize the TABLE's DDL using some API or tool, such as RTVSQLSRC.


5

I've just come across StandardCDC which might be of interest: StandardCDC captures data manipulation language (DML) changes for a specified table and stores the results in a relational format. The capture table mirrors the column list of the tracked object, with options for storing only specific columns.


5

You have a working query, but you are selecting: FROM CF30, EC01, OC02, OM01U1, RS2101F With no explicit joins and only one implicit join: WHERE OM01U1.OM01015 = RS2101F.OUTNUM This is going to lead to problems. Can you find which fields (columns) match to which in each table? You could then say: FROM OM01U1 INNER JOIN RS2101F ON OM01U1.OM01015 = ...


5

When you use openquery the query itself executed on remote server and you receive only results. In case of linked server, you local server does all the job. You may want to check http://social.msdn.microsoft.com/Forums/eu/transactsql/thread/0e68f512-1e19-4c50-b343-219085d70076


4

Triggers and audit/history tables. And columns in the main table for Inserted/Updated A history table is easily queried and can be demonstrated to an auditor Universal across different RDBMS No history write = no write because the trigger is part of the transaction From the audit/history tables you can get the state of the database at any point in time. ...


4

In addition to setting Local DTC Properties, you need to open the firewall on both machines to allow program Distributed Transaction Coordinator to go thru. When testing T-SQL in Query Editor Window, make sure to turn on XACT_ABORT as suggested by Microsoft. XACT_ABORT must be set ON for data modification statements in an implicit or explicit ...


3

Change tracking can be used in Standard Edition. It will allow you to track changes to the data, but won't be able to tell you who made them. Your options are basically the following: triggers - maybe even use instead of triggers - for an update you can move the current copy of the row to a history table, and replace it with the active copy. move to ...


3

This should probably be a comment rather than an answer since I'm not sure if my reasoning is correct, but then I wouldn't be able to have any formatting. Could you create a temporary table and JOIN instead? ...<sql statements and joins> JOIN tmp_table ON my_table.a = tmp_table AND my_table.b = tmp_table.b Edit: if the values you're checking ...


3

I've tested two tools that can provide info about transactions including when, who, using which computer and application ApexSQL Audit creates auditing triggers for you, so if you're not up to coding and creating triggers for each of your tables, this is an option It has 2 built in reports, and the good thing about it is that it saves all captured ...


3

You are not checking for an end of cursor condition. DECLARE END_CL INT DEFAULT 0; DECLARE CL CURSOR FOR SELECT LNAME , FNAME , DOB , TRANSNCNMBR , TOPOINTS FROM DB_TRANSDATA ORDER BY LNAME ASC , FNAME ASC , DOB ASC , TOPOINTS DESC ; DECLARE CONTINUE HANDLER FOR NOT FOUND SET END_CL = 1; FETCH CL INTO V_LNAME , V_FNAME , V_DOB , V_TRANSNMBR , V_POINTS , ...


3

You can use a windowing function to partition the data. If I knew your data better I'd rewrite it without a subselect, but the wall of text is making my head hurt ;) Here you go: SELECT * FROM ( SELECT ROW_NUMBER() OVER(PARTITION BY OM01U1.OM01015 ORDER BY EC01_LASMTCDAT DESC) as r, OM01U1.OM01041 as Loc, OM01U1.OM01015 AS Outlet, -- SNIP -- -- SNIP -- ...


3

Other options except the ones wrote by Aaron and gbn: use a 3rd party audit tool (redgate, lumigent...) implement custom package using fn_dblog system function (reads the transaction log) use 24/7 server traces (already mentioned by Aaron, sorry)


2

Actually, you question has less to do with "DB2" than it does with one of IBM's main products - IBM i on Power (also known as System i, iSeries, and AS/400 in the past). For whatever reason, IBM chooses to refer to a lot of things as "Objects" within their system. This gets very confusing to hear, because in and of itself, the operating system i5/OS is not ...


2

You are joining 5 files, but have only one condition that limits one of those joins. WHERE OM01U1.OM01015 = RS2101F.OUTNUM This condition should go into your join specs FROM OM01U1 JOIN RS2101F ON OM01U1.OM01015 = RS2101F.OUTNUM JOIN CF30 ON _____ = ______ JOIN EC01 ON _____ = ______ JOIN OC02 ON _____ = ______ If some of these ...


2

Figured it out!! Apparently we didn't have the latest service pack installed. We had to install the 64bit version of the service pack (32bit version failed to install), and when it finished the decimal numbers were coming across. EDIT: In case anyone was wondering: We're using "IBM iAccess for Windows 7.1 (07.01.0800) (64bit)" and using "Microsoft OLE DB ...


2

You are experiencing the difference between "system naming convention" and "SQL naming convention" modes. Originally, system naming mode required a slash character / as the delimiter when qualifying with a schema name, while SQL naming mode used a period . delimiter. An update to v7.1 now allows either delimiter when using system naming. V5R3 is no longer ...


2

One thing that you are missing in your stored procedure declaration is the DYNAMIC RESULT SETS option. The default value for that option is 0, may be that's what confuses the client software. Whether it (the client software) can even handle cursors returned by a stored procedure is another story.


2

In SQL Server you actually shouldn't need to make any changes to the outlying code or add an INSTEAD OF trigger to make this work. Here is a quick example, tested on SQL Server 2012, but should work fine on 2005 as well: CREATE TABLE dbo.source(bar INT, x UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID()); GO CREATE TABLE dbo.[target](bar INT, x UNIQUEIDENTIFIER ...


2

The solution to reporting on iSeries SQL stored procedure in SSRS is to install DB2UDB .Net provider that comes with IBM Client Access into BIDS by adding two XML tags to RSReportDesigner.config. Add the following to <Data> tag in .config: <Extension Name="DB2UDB" Type="IBM.Data.DB2.iSeries.iDB2Connection,IBM.Data.DB2.iSeries, Version=12.0.0.0, ...


1

iSeries Navigator did not "return" two statements. It echoed them in the messages tab, NOT the data results tab. SYSIBM.SYSDUMMY1 is an IBM supplied table with 1 column and one row. You got the correct answer for the procedure you wrote. SYSDUMMY1 is generally used in example code as a proxy to evaluate expressions that do not require any table. For ...


1

Start with this statement before trying to execute the function: SET PATH TESTDAT;


1

The problem was something simple yet hard to find. The indexes' sort sequence was wrong. The clients were wrongly configured to use language preference for sort sequence. So when the indexes were created they had language specification for sorting. The server when a job was submitted, was looking for indexes with *HEX specification so he was ignoring ...


1

I'm no expert on DB2 for iSeries (my only experience with it is helping others online!), so this may be entirely wrong... I think support for the standard INFORMATION_SCHEMA was only added recently. As in DB2 for LUW (which I'm rather more familiar with), INFORMATION_SCHEMA is probably just a relatively thin set of views over the "real" system catalog which ...


1

As I understand it, your development team got the error when using DbVisualizer with JTOpen (jt400.jar) for direct connections to the database. I got the same exception with DBvis ver 9.1.10. The tool took a long time when clicking the connect button, then finally showed error code -99999. What I do to fix this is to disable the Proxy setting of DBvis: ...


1

It appears to be long column name versus short column name. Upon closer inspection the hidden column always had the same value as an unhidden one. Since the long name in this case was abbreviated and cryptic, I didn't make that connection right away.


1

Yes there is a need for it, but you can help without creating the index at least in SQL Server You are joining on invoice..cust_code which is not indexed so that the Database engine will have to do a hash lookup on the table finding all invoices with the correct customer code and then filtering on the invoice id. By creating and enforcing a foreign key ...


1

If you have a license for enterprise edition, upgrade to 5.5 or 5.7 and use the audit log plugin.



Only top voted, non community-wiki answers of a minimum length are eligible