Tag Info

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

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 = ...

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.

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. ...

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 ...

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 -- ...

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 ...

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 ...

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 ...

Don't know which version of i5/OS you are on. This is from V5R4 documentation. But things you may be coming across: 1 - The total number of subselects in a fullselect (UNION or UNION ALL clause) is greater than 32. 2 - The total number of columns, constants, and operators is greater than the SQL limits. 3 - The sum of the lengths of the non-LOB columns in a ...

This is not an easy task to translate from one flavor of SQL to another. That's either (kind of) expensive or difficult to do for yourself. No database language flavor is really easy to digest and translate into another, because all have lots of system procedures, data types, functions and others that are really system specific. Each SQL flavor implements ...

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 ...

When you are overwhelmed with a long complex query, involving so many pieces, it is often a good strategy to break it down into smaller parts, get them working, then build it back up, in small stages. Focus first on a "mind-sized bite". Don't bite off more than you can chew in a single mouthful. Start simple, and add things back in layers. You probably ...

Here is documentation for DB2 for i on i5/OS V6 R1. Here is specific to DB2 itself. Here is specific to DB2 SQL.

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 ...

SQL2008 has a new feature called CHANGETABLE that you can use. Check this link to read more about it. CHANGETABLE returns change tracking information for a table. You can use this statement to return all changes for a table or change tracking information for a specific row.