Database Configuration In DB2 and Oracle

In general, there are no equivalent settings.  Checking for deadlocks is something that happens automatically, there is no setting that configures when that checking is done.  Oracle generally has no system-level timeout for locks.  You can set DISTRIBUTED_LOCK_TIMEOUT but that only affects how long a distributed transaction would wait for a lock to be acquired, it has no impact on non-distributed transactions.  Beyond that, a session will wait for a lock indefinitely unless the session requesting the lock specifies a timeout (i.e. SELECT ... FOR UPDATE WAIT n).


DB2 select from final table update gives SQL0199 error

Looking at the Db2 for i 7.2 Knowledge Center, shows that only INSERT's are valid in data-change-table-references, not UPDATE's

https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_72/db2/rbafztabref.htm

data-change-table-reference

|----+-FINAL-+--TABLE--(--INSERT statement--)--+--------------------+----|
     '-NEW---'                                 '-correlation-clause-'

  If a data-change-table-reference is specified, the intermediate result table is the set of rows inserted by the INSERT statement.

The link you referenced in your original question is for DB2 for LUW (Linux, Unix and Windows) 9.7. This is essentially a different product than Db2 for i.


script to show all the permissions for a table

I found a good script to see the permissions on a database object.

SELECT
  (
    dp.state_desc + ' ' +
    dp.permission_name collate latin1_general_cs_as +
    ' ON ' + '[' + s.name + ']' + '.' + '[' + o.name + ']' +
    ' TO ' + '[' + dpr.name + ']'
  ) AS GRANT_STMT
FROM sys.database_permissions AS dp
  INNER JOIN sys.objects AS o ON dp.major_id=o.object_id
  INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id
  INNER JOIN sys.database_principals AS dpr ON dp.grantee_principal_id=dpr.principal_id
WHERE 1=1
    AND o.name IN ('YOUR TABLE NAME')      -- Uncomment to filter to specific object(s)
--  AND dp.permission_name='EXECUTE'    -- Uncomment to filter to just the EXECUTEs
ORDER BY dpr.name

the script above works for a database object.
however,
when I add permissions on the schema where the db object is, the above script does not pick that up, for example:

grant delete on schema::dbo to [db_webUser]
GO

so for that case I found this script here that caters for that as well:

------------------------------------------------------------------------------------------------------------------
-- http://schottsql.blogspot.co.uk/2011/02/quickly-script-permissions-in-sql-2005.html
------------------------------------------------------------------------------------------------------------------

SELECT
state_desc + ' ' + permission_name +
' on ['+ ss.name + '].[' + so.name + ']
to [' + sdpr.name + ']'
COLLATE LATIN1_General_CI_AS as [Permissions T-SQL]
FROM SYS.DATABASE_PERMISSIONS AS sdp
JOIN sys.objects AS so
     ON sdp.major_id = so.OBJECT_ID
JOIN SYS.SCHEMAS AS ss
     ON so.SCHEMA_ID = ss.SCHEMA_ID
JOIN SYS.DATABASE_PRINCIPALS AS sdpr
     ON sdp.grantee_principal_id = sdpr.principal_id
where 1=1
  AND so.name = 'ItemStock'

UNION

SELECT
state_desc + ' ' + permission_name +
' on Schema::['+ ss.name + ']
to [' + sdpr.name + ']'
COLLATE LATIN1_General_CI_AS as [Permissions T-SQL]
FROM SYS.DATABASE_PERMISSIONS AS sdp
JOIN SYS.SCHEMAS AS ss
     ON sdp.major_id = ss.SCHEMA_ID
     AND sdp.class_desc = 'Schema'
JOIN SYS.DATABASE_PRINCIPALS AS sdpr
     ON sdp.grantee_principal_id = sdpr.principal_id
where 1=1

order by [Permissions T-SQL]
GO

when I run the script above I get the following result, which includes the permission on the schema that reflects on my table.


You are correct that it is not a good practice to bundle the permission check in the relational query. The extra predicates can make the optimizer's choice more complicated, and the code is not very readable. It also may cause the optimizer to process all the data from the actual SELECT you are looking for, even if the permission check ends up failing, and returning an empty set.

What if instead, you split it into 2 phases - first check for the permissions, and then execute your query only if permissions check succeeds.
For example:

IF (
    -- All Broker
    ( 'True' = (
                SELECT AllBrokers
                FROM   SiteUser
                WHERE  ID = @SiteUserID
                )
    )
    OR
    ( A.BrokerID IN (
                     SELECT BrokerID
                     FROM   SiteUserBroker
                     WHERE  SiteUserID = @SiteUserID
                    )
    )
   )
SELECT *
FROM TABLE A  <+ Joins>
WHERE <Conditions>
ELSE
SELECT NULL AS ColumnA, NULL AS ColumnB... -- all columns as the * above
WHERE 1=0 -- Forces an empty set

This will keep the same output columns of the set either if permission fail, or succeed, but will skip the actual SELECT processing if it fails. It is also much more readable IMHO.

An even better approach would be to raise an explicit access denied message, for example:

ELSE
THROW <Access Denied Error>

However, this approach may require application code changes to handle the message correctly.

BTW - never use SELECT * in production code, see this thread...

HTH


Issue with CAST using linked servers (possible bug?)

Do you have the table definition for the table Detection?  I'm betting that infoFlag might not be set up as a bigint, but rather as a numeric with no decimal points.  You might also make sure that the data type is consistant on all of your servers.


My answer is a direct quote of this link:

http://www.sqlservercentral.com/Forums/Topic960549-391-1.aspx#bm960643

There is no direct way of finding the creation date of an index. But however if you look in sysobjects there is a creation date for Primary Key or Unique Constraints.Indexes associated with primary Primary Key or Unique Constraints creation date can be known.

check if there are any new DMVs (Dynamic Mgmt Views) in sql server 2008 which can help you in getting creationDate.

try this query

select crdate, i.name, object_name(o.id)
from sysindexes i
   join sysobjects o ON o.id = i.id
order by crdate desc


Problem with union casting integer to ceiling(decimal)

Looks like a bug to me and I can confirm this puzzling behaviour in:

10.2.14-MariaDB

If possible you can cast the integer value to a double:

SELECT cast(20 as double) UNION SELECT null UNION SELECT 2.2;

or make sure you have the double value first:

SELECT 2.2 UNION SELECT null UNION SELECT 22;

Further observations after reading the comments in @Evan Carroll's answer

select 20 union select null union select 2;
+------+
| 20   |
+------+
|   20 |
| NULL |
|    2 |
+------+

Ok, using int values does not seem to produce the error.

select 20 union select null union select 9.0;
+------+
| 20   |
+------+
| 9.9  |
| NULL |
| 9.0  |
+------+

ERROR: Seems like output is decimal(2,1)

create table tmp as select * from (select 20 as x
                                   union
                                   select null
                                   union
                                   select 9.0) as t

describe tmp;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| x     | decimal(2,1) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+

The error is not isolated to the command line interface, it exists for python2-mysql-1.3.12-1.fc27.x86_64 as well:

>>> import MySQLdb
>>> db = MySQLdb.connect(host="localhost", user="*****", passwd="*****", db="test")
>>> cur = db.cursor()
>>> cur.execute("SELECT 20 union select null union select 2.2")
3L
>>> for row in cur.fetchall() :
...     print row
...
(Decimal('9.9'),)
(None,)
(Decimal('2.2'),)

Oddly enough the error disappears if null is moved first or last:

select null union select 20 union select 9.0;
select 20 union select 9.0 union select null;

+------+
| NULL |
+------+
| NULL |
| 20.0 |
| 9.0  |
+------+

If null is placed first, resulting type is decimal(20,1). If null is placed last resulting type is decimal(3,1)

The error also disappears if another leg is added to the union:

select 20 union select 6 union select null union select 9.0;
+------+
| 20   |
+------+
| 20.0 |
| 6.0  |
| NULL |
| 9.0  |
+------+

resulting type decimal(20,1)

adding another null in the middle preserves the error:

select 20 union select null union select null union select 9.0;
+------+
| 20   |
+------+
| 9.9  |
| NULL |
| 9.0  |
+------+

But adding a null at the beginning fixes it:

select null union select 20 union select null union select null union select 9.0;
+------+
| NULL |
+------+
| NULL |
| 20.0 |
| 9.0  |
+------+

As expected casting first value to decimal(3,1) works.

Finally, explicitly casting to decimal(2,1) produces the same error but with a warning:

select cast(20 as decimal(2,1));
+--------------------------+
| cast(20 as decimal(2,1)) |
+--------------------------+
| 9.9                      |
+--------------------------+
1 row in set, 1 warning (0.00 sec)


Database layout to keep track of the "freshness" of a column value in a specific row

With MariaDB 10.3 you can use system-versioned tables.

You can then see all the versions of a particular row with a query like:

SELECT *, ROW_START, ROW_END FROM Person WHERE id = 4 FOR SYSTEM_TIME ALL;

Also, with the AS OF syntax you can get the version of the row that existed at a particular time:

SELECT * FROM Person WHERE id = 4 FOR SYSTEM_TIME AS OF TIMESTAMP '2019-08-08 15:10:00';

See also this post in the MariaDB blog: Automatic Data Versioning in MariaDB Server 10.3.

EDIT

If you need to know who made a change, this will probably have to be controlled through your software. You can add a special last_change_by column in the table e.g. something like:

ALTER TABLE Person ADD last_change_by varchar(100);


Make a DBMS-enforced multi-column "UNIQUE" constraint, with a "match all" wildcard

I suggest you fix your design. Using NULL as a wildcard is not the best in my opinion. Splitting the table into 2 or 3 so the wildcard information is stored in separate tables would be better.

If you keep this design, it will need some complex exclusion constraints, besides the UNIQUE one:

CREATE TABLE test (
    test_id serial NOT NULL PRIMARY KEY,
    x int NULL,
    y int NULL,

-- unique constraint for non-null (x,y) values
CONSTRAINT x_y_unique
    UNIQUE (x,y),

    -- when x IS NULL, do not allow a non-null x value (with same y)
    CONSTRAINT unique_with_wildcards_x_null
        EXCLUDE USING GIST
        ( (CASE WHEN x IS NULL THEN 0 ELSE 1 END) WITH <>,
          y with =
        ),

    -- do not allow 2 NULL x values (with same y)
    CONSTRAINT unique_with_wildcards_x_one_null
        EXCLUDE USING GIST
        ( (CASE WHEN x IS NULL THEN 0 ELSE NULL END) WITH =,
          y with =
        ),

    -- when y IS NULL, do not allow a non-null y value (with same x)
    CONSTRAINT unique_with_wildcards_y_null
        EXCLUDE USING GIST
        ( x with =,
          (CASE WHEN y IS NULL THEN 0 ELSE 1 END) WITH <>
        ),

    -- do not allow 2 NULL y values (with same x)
    CONSTRAINT unique_with_wildcards_y_one_null
        EXCLUDE USING GIST
        ( x with =,
          (CASE WHEN y IS NULL THEN 0 ELSE NULL END) WITH =
        )
) ;

Tested in dbfiddle.uk.

Notice that the (NULL, NULL) combination is allowed. If you want this to be treated as a super-wildcard, (so if it's in the table, no other rows would be allowed), you'll need some more exclusion constraints.


Yes and no.

Queries showing a high cost are usually a good place to start, when looking to tune a query. They are places where the query planner believed the query would spend a larger amount of its time and effort to get the query results.

However, when you've got the actual query numbers, one of the key things to check is how the estimate and the actual query run matched up. One of the first things to fix, when possible, are places where there's a big difference between the actual and the estimated numbers. For example, if the estimated and the actual rows are way off, then capturing/updating statistics for the table in question can result in significant gains.

Taking this into account, it may well be transactions that the query planner expected not to be significant, but where the estimated numbers are much smaller than the actual numbers, that are the transactions that most need tuning.

When estimated and actual numbers match well, then those high cost operations are again a good place to focus your tuning efforts. However, they shouldn't be the only thing you pay attention to.


On Db2 for LUW you can use this query. You get one row per table data partition (1 row for a non range partitioned table).

SELECT
    SD.TBSPACE AS DATA_SPACE
,   SL.TBSPACE AS LONG_SPACE
,   SI.TBSPACE AS INDEX_SPACE
FROM
          SYSCAT.DATAPARTITIONS P
JOIN      SYSCAT.TABLESPACES   SD ON SD.TBSPACEID = P.TBSPACEID
LEFT JOIN SYSCAT.TABLESPACES   SL ON SL.TBSPACEID = P.LONG_TBSPACEID
LEFT JOIN SYSCAT.TABLESPACES   SI ON SI.TBSPACEID = P.INDEX_TBSPACEID
WHERE
    TABSCHEMA = 'YOUR_SCHEMA'
AND TABNAME   = 'YOUR_TABLE'


Unable to connect with root because of skip-name-resolve option

The issue may be that you have 2 entries for root, and that root@% doesn't have the same password as the root@localhost. Changing options may change the order MySQL uses each entries.


Put backticks around partition like `partition`. It's a reserved word since version 5.6.2. See 9.3 Keywords and Reserved Words.


Business Intelligence Development Studio installation for Visual studio 2010 problem

From your screenshot I judge that you are using the Express installer (It says: Select the express features to install), SSDT and Visual Studio 2010 have no influence on the options in this installer.

Make sure you install the Microsoft SQL Server2008 R2 RTM - Express with Advanced Services  as that is the only one including BIDS.

Have a look at Features Supported by the Editions of SQL Server 2008 R2 which clearly states that the regular express doesn't have BIDS and the Express with advanced services does have BIDS.

Installing BIDS has no prerequisite on Visual Studio or SSDT and SSDT has no influence whatsoever on the installer you are running.

Also, BIDS is by definition Visual Studio 2008 as explained here

  Business Intelligence Development Studio is Microsoft Visual Studio
  2008 with additional project types that are specific to SQL Server
  business intelligence.

If you're insisting on installing Visual Studio of a specific version you need to look into SQL Server data tools. SQL Server Data tools is not shown in the installation features but is a separate download and can not be reasonably used with SQL 2008


I cant offer you to dump this to the errorlog. For that you need to do some serious research and both these options are lightweight and can leave you with a simple file to look at.

In SQL Server 2005 you need to create a server side trace on transaction filtering on eventsubclass 2.

In SQL Server 2008R2 and later you are better off creating an extended event session on the
SQLSERVER.SQL.TRANSACTION event filtering on transaction_state = 2

But since you are using SQL Server 2005 you need a trace, here is a script that will create a server side trace capturing all rollback events:

-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5

-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically.

exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, NULL
if (@rc != 0) goto error

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 50, 11, @on
exec sp_trace_setevent @TraceID, 50, 6, @on
exec sp_trace_setevent @TraceID, 50, 8, @on
exec sp_trace_setevent @TraceID, 50, 10, @on
exec sp_trace_setevent @TraceID, 50, 12, @on
exec sp_trace_setevent @TraceID, 50, 13, @on
exec sp_trace_setevent @TraceID, 50, 15, @on
exec sp_trace_setevent @TraceID, 50, 34, @on
exec sp_trace_setevent @TraceID, 50, 35, @on
exec sp_trace_setevent @TraceID, 50, 60, @on
exec sp_trace_setevent @TraceID, 50, 64, @on

-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
set @intfilter = 2
exec sp_trace_setfilter @TraceID, 21, 0, 0, @intfilter

-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID
goto finish

error:
select ErrorCode=@rc

finish:
go

For this to work for you, you will need to setup the trace to start with the server.

For that you need to make sure that the filename is unique each time the trace is started, create a procedure from the trace definition and make it a startup procedure.


Sizing MySQL Database Servers for Drupal

I was waiting for more experienced people to answer, but one thing I will point out, since you're using innodb engine. You'll want sizeable amount of RAM to increase the innodb_buffer_pool_size so that it can hold your dataspace, if possible. So if your database takes up 6GB, it's feasible to set the buffer_pool_size to catch it in RAM (assuming you can address that much RAM...see below). But if the database takes up 64GB, it might be a stretch (at least for something like my small company's budget) to get a server with enough RAM.

You'll need to make sure your CPU architecture and OS is 64-bit. I ran into this recently, where an old xserve upgraded to Mac OSX 10.6 can't utilize >4GB of RAM per process. Probably not an issue with modern servers.


Is there a way to view WAL updates on a Hot Standby server?

pg_stat_statements has nothing to do with WAL. The WAL doesn't record statements and you cannot discover what statements were run from WAL. It only records blocks changed on disk and relevant related metadata.

It's definitely not useful on a standby.

You could use pg_xlogdump (pg_waldump on pg10 and newer) to view the low level WAL stream, but without a lot of experience and time you won't be able to understand it. It doesn't and can't show table names and row info, only table relfilenodes and tuple TIDs, plus binary tuple data.

If you want to avoid installing pg_stat_statements you could make a poor-man's version by capturing pg_stat_activity on a timer and doing your own parsing and merging.


Mysql Order , Select highest value item and update to another table

In this case you can use INSERT INTO ... SELECT FROM ... ORDER BY DESC LIMIT 1:

INSERT INTO data_2 (name, text1, text2)
SELECT name, serial, CAST(point AS varchar(10))
FROM data_1
ORDER BY point DESC
LIMIT 1


Executing SSIS catalogue package from a different machine, authentication fail

You're on the right track, but I suspect you're still running into the double-hop issue.  SPNs are a step in the right direction to resolve this, but next you need to setup delegation for your SQL Server Instance so that it can properly pass along the AD token.

I will recommend that you setup constrained delegation for SQL Server, but any delegation adjustments will require an account with Domain Admin privileges on your network, so you may need to loop in another person or team depending on your company's structure.

The link above is for the older versions of Active directory, but the same approach will likely apply if you're running a newer version.

Steps 3 - 6 on this article about configuring delegation for SSRS are also a good reference point and SSIS is no different from SSRS where these steps are concerned.


For partition pruning to work, it is important that your partition key is simple and part of the where clause of your queries. In your example you can simplify to
check((extract(hour from "timestamp")) = 0  # equality
check((extract(hour from "timestamp")) = 1  # etc.
check((extract(hour from "timestamp")) = 23

At this time it would be a nice use case for timescaleDB which does a lot of this all automatically for you.


Monthly Date and Hour wise report generation using PHP MYSQL

How to get data without dates which are not recorded?

This is a common problem.  The generic solution is

Build a table with all possible dates (at least in the range needed).
LEFT JOIN from that table to your table.  (Or, if it is a complex query, then use a derived table:  LEFT JOIN (SELECT ...) AS x  ON dates.day = x.day.
If desired, use COALESCE(...) to turn NULLs into 0s or blanks.


You can't do this in Teradata like in Oracle.

As a workaround you might Insert into a temporary table and either run a Select on it at the end of the SP or Select from the client after the CALL.

-- create the message table once
CREATE GLOBAL TEMPORARY TABLE DBMS_OUTPUT
 (
   TS TIMESTAMP(2) DEFAULT Current_Timestamp(2),
   Step VARCHAR(100),
   ErrorMsg VARCHAR(200)
 ) ON COMMIT PRESERVE ROWS;

-- now create your SP
REPLACE PROCEDURE mySP (...)
DYNAMIC RESULT SETS 1
BEGIN

   ...

    -- clear messages from previous CALL at start of SP
   DELETE FROM DBMS_OUTPUT;

   ...

   -- add a message
   INSERT INTO DBMS_OUTPUT (Step, ErrorMsg) VALUES ('step X', 'bla bla bla');

   ...

   -- finally return all messages (also available after the CALL)
   BEGIN
      DECLARE qry VARCHAR(100);
      DECLARE ErrorMsgs CURSOR WITH RETURN ONLY FOR c;
      SET qry = 'SELECT * FROM DBMS_OUTPUT ORDER BY TS;';
      PREPARE c FROM qry;
      OPEN ErrorMsgs;
   END;

END;


Are older versions of MS SQL Server DEVELOPER edition now free?


How does MySql(InnoDB) update secondary non unique index?

Every InnoDB table has a special index called the clustered index where the data for the rows is stored (in other words clustered index contains all columns of the table). By default InnoDB uses primary key as clustered index. So

when you update a row in clustered index MySQL server uses other columns values to find corresponding records in non-clustered indexes and update them.
All non-clustered indexes contain key columns of the clustered indexes. This way MySQL server can determine corresponding row in clustered index using these values.

Reference: 14.6.2.1 Clustered and Secondary Indexes (MySQL 5.7 Reference Manual)


Linkserver update not working in Transaction

I have tried this on SQL Server 2012. If I run your query as is it completes successfully but does not update anything. But if I do SET xact_abort ON it completes successfully. You can read more about xact_abort on  https://msdn.microsoft.com/en-us/library/ms188792.aspx.

SET xact_abort ON

BEGIN try
    BEGIN TRANSACTION trf

    update [SERVER].[testdb].[AAA].[MyTable]
    set MyName= 'abc@pqr.com'
    where ID=999

    --COMMIT TRAN trf
END try

BEGIN catch
    ROLLBACK TRANSACTION trf
END catch

What is the SQL Server version you are trying this on? Also, tell us on which line you are getting incorrect parameter error?


COUNT before JOINing, else you will be counting the result of the JOIN.
Possibly it is as simple as putting the C counts in the C subquery; ditto for O.  At that point, the subqueries may as well deliver only counts, not all the other columns.  And, then, why JOIN?


Force MS SQL Service Account to re-authenticate

When a Windows account logs on to the domain the session receives a ticket that includes all of the group memberships.  Accordingly, if you add the account to a new group, it will not be in effect for sessions that are already logged in.  So you'll need to either:

Restart the SQL Server service
Add the user account specifically to the share permissions as a temporary solution until the next time the SQL Server service is restarted
Change the path of the backup config to use an IP address instead of a servername--again, temporarily.  This will cause the connection to bypass Kerberos and use NTLM authentication, which will pick up the group membership (not 100% sure this will work for you, but it's totally a thing).


Is there a way to construct SQL statement to return desired value only if there is a single row found, and return 0 otherwise?

One way is using COUNT and MIN/MAX:

SELECT
    CASE WHEN COUNT(*) > 1 THEN 0 ELSE MIN(contact_id) END
FROM
    contacts
WHERE
    customer_office_id = [int]
GROUP BY
    customer_office_id;

db<>fiddle here

To cover the case when there are no rows returned you can use:

SELECT
    COALESCE((SELECT
                  CASE WHEN COUNT(*) > 1 THEN 0 ELSE MIN(contact_id) END contact_id
              FROM
                  contacts
              WHERE
                  customer_office_id = [INT]
              GROUP BY
                  customer_office_id), 0) contact_id;

db<>fiddle here


Oracle: Database writers idle until 'Checkpoint not complete' even with multiple redo logs

I guess you could play with the fast_start_io_target and log_checkpoint_timeout initialization parameters to entice database writers to write out dirty blocks more aggressively. Unless your redo logs are on a different I/O path from your data files, you are unlikely to see any benefits from this though, because both log and database writers will compete for the same I/O bandwidth. A better choice, as others have mentioned, would be to increase the size of your redo logs to accommodate long-running transactions.


How dangerous is granting the ALTER TABLE permission?

Pretty dangerous...

In addition to the obvious permission to change the structure of StagingTable itself the ALTER TABLE permission allows them to create triggers on the table. So in this case through ownership chaining they are able to both see sensitive customer data (despite the explicit DENY permissions) and perform vandalism on this second table.

EXECUTE AS user='etlUser'

GO

CREATE OR ALTER TRIGGER TR ON dbo.StagingTable AFTER UPDATE AS
/*Exposure of sensitive data*/
SELECT * FROM dbo.Customer;

/*Vandalism*/
DELETE FROM dbo.Customer;

go

--Fire the trigger
UPDATE dbo.StagingTable SET SomeData = SomeData WHERE 1=0;

REVERT


First of all, you have an additional parenthesis before : character, expression is:

FINDSTRING(
SecurityDescription,"\n",1) > 0
? SUBSTRING(SecurityDescription, 1,
            FINDSTRING(SecurityDescription,"\n",1) - 1)
 : SecurityDescription

Lineage ID Error

"Lineage ID is a property of the component or transformation used in the data flow task. It contains an integer value that will work as buffer pointer. Each column in the data flow task will be assigned a lineage ID." Read about lineage ID in this Microsoft TechNet article

LINEAGE ID Error implies that a Source metadata was changed, just re-validate source (connection and component) by double click on the the derived column transformation and close it , then check the columns metadata (using the advanced editor). (Note that when double-clicking on a component that contains errors it will prompt to fix it)

Or you can try removing Derived Column transformation and adding it again (if previous solution doesn't works)

Also it is good to revalidate Source columns metadata

Analyzing the exception

If you take a look at the exception thrown you will see that SecurityDescription has different lineage id in the same expression which is not right.

  "[FINDSTRING](#102,"\n",1) > 0 ? [SUBSTRING](#11898,1,[FINDSTRING](#11898,"\n",1) - 1) : #102"

So it is clear that metadata needs to be updated.

Also from the error screenshot it looks like the expression is using SecurityDescriptionNoLineFeeds not
SecurityDescription.

These errors maybe caused during the package upgrade process

Side Note: check that SecurityDescription is not defined multiple time in differents components. (Ex: Source.SecurityDescription and Script.SecurityDescription)

Try dropping derived column transformation and re-creating it or add a new derived column and drop the old one


https://mariadb.com/kb/en/configuring-mariadb-for-remote-client-access/ says :

  MariaDB only supports one bind-address, but binds to 0.0.0.0, or ::
  (every IP) if the bind-address directive is left out).

I understand that in this case mariadb listens on every IP address of the machine. However even in this case you can filter on hostname at user account level with the user@host syntax.


Looks like you want something like:

SELECT country
, sum(case when type = 'first' then 1 else 0 end) as type_first
, sum(case when type = 'second' then 1 else 0 end) as type_second
, sum(case when type = 'third' then 1 else 0 end) as type_third
FROM table1
GROUP BY country


You can add the prefix SAFE so the DATE function returns NULL instead of throwing an error.
https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators


how to create and insert into a table in an anonymous block

Generally, it doesn't make sense to create tables in anonymous blocks let alone to then insert data into those tables in the same anonymous block.  Creating new objects at runtime is a bad idea (hopefully you're not trying to create a temporary table like you would with other databases).

If you really do need to create a new table at runtime, you can insert data in the same anonymous block only by using dynamic SQL for the INSERT operation as well.  You would need to use dynamic SQL every time you referenced the newly created table in the same anonymous block.  You could also create the table in one anonymous PL/SQL block and then insert the data in a second anonymous PL/SQL block.  That would not require that the INSERT statement use dynamic SQL.

Since it sounds from the comments like you are trying to create a temporary table like you would in other databases, you probably want to take a look at this StackOverflow thread that discusses alternatives to creating temporary tables in Oracle.


Mysql Master-Master Replication Topologies on >2 machines

A ring can be a very good idea under certain conditions

For servers A, B, C

Server A

Master of B
Slave of C

Server B

Master of C
Slave of A

Server C

Master of A
Slave of B

All three servers have 3 databases (db1, db2, and db3)

Here is the only setup that protects the three databases

restrict all reads and writes to db1 on Server A
restrict all reads and writes to db2 on Server B
restrict all reads and writes to db3 on Server C

Replication would do the following in respone

backup database db1 by replicating to db2 and db3
backup database db2 by replicating to db3 and db1
backup database db3 by replicating to db1 and db2

IMHO performing writes (INSERTs,UPDATEs,DELETEs) for db1 to server A,B,C makes all auto_increment values separate and distinct. This would make it brittle to restore data to other boxes.

More to come...

UPDATE

I have another crazy idea

Have you ever heard of a star topology? I posted stuff earlier on this subject

https://serverfault.com/questions/264374/mysql-in-star-topology/264444#264444
https://stackoverflow.com/questions/5273043/mysql-in-star-topology/5781504#5781504

Create a master with two slaves

For servers A, B, C

Server A

Master of B and C
All tables use storage engine BLACKHOLE

Server B

Slave of A
All user tables use storage engine InnoDB

Server C

Slave of A
All user tables use storage engine InnoDB

Benefits

Post all writes (INSERTs, UPDATEs, DELETEs) to serve A
Writes should be fast becasue BLACKHOLE tables map to /dev/null
Replication actual posts real data on Slaves (Server B and Server C)
no actual data will reside in server A, only binary logs
All SELECTs can go between Server B and Server C via a Load Balancer
Slaves would virtually be identical into terms of content

Drawbacks

Once you set this up you cannot freely use ALTER TABLE statements
If replication breaks at Server A, it stops updating the Slaves


Fetching recent/historical queries run on a database from an application

There is no such thing enabled by default in the database. V$ or DBA_HIST views are not guaranteed to contain all your SQL statements all the time. The amount of memory to store SQL statements in V$ views is limited, and DBA_HIST views contain only sampled data. Furthermore, they do not store information about constraint violation errors.

If you want to find the SQLs causing unique constraint violation errors, you can create a trigger that catches the errors and logs information, for example:

create table userLog
(error_date date,
 username varchar2(30),
 error_msg clob,
 error_sql clob);

create or replace trigger log_errors after servererror on database
declare
   sql_text ora_name_list_t;
   msg clob := null;
   stmt clob := null;
   errnum number;
begin

   errnum := ORA_SERVER_ERROR(1);

   if (errnum = 1) then
       for i in 1 .. ora_server_error_depth loop
          msg := msg||ora_server_error_msg(i);
       end loop;
       for i in 1..ora_sql_txt(sql_text) loop
          stmt := stmt||sql_text(i);
       end loop;
       insert into userLog(error_date,username,error_msg,error_sql)values (sysdate,ora_login_user,msg,stmt);
   end if;
end;
/

The above trigger catches number 1 errors (that is for ORA-00001: unique constraint () violated) on the top of the stack (that is ORA_SERVER_ERROR(1)) in the database, and logs them in the userlog table with the SQL text, so you can find later the SQL statements that caused them.


How to create a stored procedure with several delete in(select) statements?

if the statements are inside the stored procedure together, they will be processed simultaneously and because the tables are related that way it will cause a problem.

No, the statements in a stored procedure are processed sequentially, not in parallel.  A stored procedure executes in a single thread, just as if you had issued the queries one after another... not simultaneously.

There are some optimizations you could make, however.

If the `titnum` table doesn't have a multi-column index on the three columns (titvalpag, titemi, titnum) together, you should probably add one.

The delete statements can be rewritten without the IN().

Use caution with this, because doing it incorrectly will remove the wrong data.

delete from mov_pedido
where pednot in(select distinct titnum
                  from mov_titulo
                 where titnum like 'A4%'
                   and titemi <='2012-01-01' and titvalpag >0);

...becomes...

DELETE p
  FROM mov_pedido p
  JOIN mov_titulo m ON m.titnum = p.pednot
 WHERE m.titnum like 'A4%' and m.titemi <='2012-01-01' and m.titvalpag >0;

This is the multi-table delete syntax.  "DELETE p" will only remove the rows from "p" (an alias for mov_pedido) that match rows in "m" (mov_titulo).  It won't remove the rows from "m" unless you say "DELETE p, m" -- so don't do that.

The mov_pedido table (and the others) should have an index on the column you're using to join (pednot, etc.) if they don't already.


Mongodb consumes too much RAM during replica resync

What uses that much memory during the resync?

There is so many things uses the memory during the resync time, As Mr. SpiXel has clearly defined here The WiredTiger cache settings only controls the size of memory directly used by the WiredTiger storage engine (not the total memory used by mongod). Many other things are potentially taking memory in a MongoDB/WiredTiger configuration, such as the following:

WiredTiger compresses disk storage, but the data in memory are
uncompressed.
WiredTiger by default does not fsync the data on each commit
WiredTiger keeps multiple versions of records in its cache
WiredTiger Keeps checksums of the data in cache
MongoDB itself consumes memory to handle open connections,
aggregations, serverside code and etc.

The maximum size of the internal cache that WiredTiger will use for all data.

Changed in version 3.4: Values can range from 256MB to 10TB and can be a float. In addition, the default value has also changed.

Starting in 3.4, the WiredTiger internal cache, by default, will use the larger of either:

50% of (RAM - 1 GB), or
256 MB.

For example, on a system with a total of 4GB of RAM the WiredTiger cache will use 1.5GB of RAM (0.5 * (4 GB - 1 GB) = 1.5 GB). Conversely, a system with a total of 1.25 GB of RAM will allocate 256 MB to the WiredTiger cache because that is more than half of the total RAM minus one gigabyte (0.5 * (1.25 GB - 1 GB) = 128 MB < 256 MB).

  How do I effectively analyze the memory consumption?

As per MongoDB blog here some of the  by @CASEY DUNHAM, MongoDB performance is a huge topic encompassing many areas of system activity.
By default, MongoDB will reserve 50 percent of the available memory for the WiredTiger data cache. The size of this cache is important to ensure WiredTiger performs adequately. It’s worth taking a look to see if you should alter it from the default. A good rule of thumb is that the size of the cache should be big enough to hold the entire application working set.

How do we know whether to alter it? Look at the  cache usage statistics:

> db.serverStatus().wiredTiger.cache
{
    "tracked dirty bytes in the cache" : <num>,
    "tracked bytes belonging to internal pages in the cache" : <num>,
    "bytes currently in the cache" : <num>,
    "tracked bytes belonging to leaf pages in the cache" : <num>,
    "maximum bytes configured" : <num>,
    "tracked bytes belonging to overflow pages in the cache" : <num>,
    "bytes read into cache" : <num>,
    "bytes written from cache" : <num>,
    "pages evicted by application threads" : <num>,
    "checkpoint blocked page eviction" : <num>,
    "unmodified pages evicted" : <num>,
    "page split during eviction deepened the tree" : <num>,
    "modified pages evicted" : <num>,
    "pages selected for eviction unable to be evicted" : <num>,
    "pages evicted because they exceeded the in-memory maximum" : <num>,
    "pages evicted because they had chains of deleted items" : <num>,
    "failed eviction of pages that exceeded the in-memory maximum" : <num>,
    "hazard pointer blocked page eviction" : <num>,
    "internal pages evicted" : <num>,
    "maximum page size at eviction" : <num>,
    "eviction server candidate queue empty when topping up" : <num>,
    "eviction server candidate queue not empty when topping up" : <num>,
    "eviction server evicting pages" : <num>,
    "eviction server populating queue, but not evicting pages" : <num>,
    "eviction server unable to reach eviction goal" : <num>,
    "internal pages split during eviction" : <num>,
    "leaf pages split during eviction" : <num>,
    "pages walked for eviction" : <num>,
    "eviction worker thread evicting pages" : <num>,
    "in-memory page splits" : <num>,
    "in-memory page passed criteria to be split" : <num>,
    "lookaside table insert calls" : <num>,
    "lookaside table remove calls" : <num>,
    "percentage overhead" : <num>,
    "tracked dirty pages in the cache" : <num>,
    "pages currently held in the cache" : <num>,
    "pages read into cache" : <num>,
    "pages read into cache requiring lookaside entries" : <num>,
    "pages written from cache" : <num>,
    "page written requiring lookaside records" : <num>,
    "pages written requiring in-memory restoration" : <num>
}

There’s a lot of data here, but we can focus on the following fields:

wiredTiger.cache.maximum bytes configured: This is the maximum cache
size.
wiredTiger.cache.bytes currently in the cache – This is the size of
the data currently in the cache. This should not be greater than the
maximum bytes configured.
wiredTiger.cache.tracked dirty bytes in the cache – This is the size
of the dirty data in the cache. This value should be less than the
bytes currently in the cache value.

Looking at these values, we can determine if we need to up the size of the cache for our instance. Additionally, we can look at the wiredTiger.cache.bytes read into cache value for read-heavy applications. If this value is consistently high, increasing the cache size may improve overall read performance.

  How do I prevent the primary from going down because of the resync?

As per MongoDB blog documentation here There might be a situation when primarily becomes inaccessible. When a primary does not communicate with the other members of the set for more than 10 seconds, an eligible secondary will hold an election to elect itself the new primary.
The first secondary to hold an election and receive a majority of the members’ votes becomes primary.
Although the timing varies, the failover process generally completes within a minute. For instance, it may take 10-30 seconds for the members of a replica set to declare a primary inaccessible. One of the remaining secondaries holds an election to elect itself as a new primary. The election itself may take another 10-30 seconds.
While an election is in the process, the replica set has no primary and cannot accept writes and all remaining members become read-only.

For further your ref here , here and here


Distribution Server (1) Replicating Source Server (2) To Destination Server (3)

I'm not sure if this helps you or not, but In SSMS in all versions (2005+), if you haven't configured Distribution, you'll see the option configure distribution,

but if you've configured a distribution or have one on the server, then you'll have the below menu and won't see configure distribution.


Connectivity timeout between cluster nodes

Have you checked SQL error logs? Start with what the description of errors. Is it because it lost quorum? Is there high CPU in any of the nodes? Are you on FCI or AAG?

We had to adjust timeout between our replicas when the databases were reporting unhealthy during high peak ETL loads.

0
down vote
Set up trace to investogate what other things are running on the server. Then check SQL wait stats. https://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/

If there is no load - increase your timeout to a higher value (https://blogs.msdn.microsoft.com/alwaysonpro/2014/06/03/connection-timeouts-in-multi-subnet-availability-group/)) and see if the errors go away. However, if you have no problems in your workload then you should not worry about these errors. First you need to assess if users are complaining of slowness, are there any jobs failing during this time? You have to ask these questions before trying to simply reposlve an error.


SQL Data warehouse and normalization forms

Actually it is common to denormalise in a data warehouse.

For instance I will  have the full address of customers with the state and the country as columns in my Customer dimension. The data will be duplicated a lot for the customers living in the same city, but I won't have to do two costly joins to retrieve the state and then the country from two other tables.

Most of Data Warehouse are modelled as Star Schemas, where the fact table (the event you want to measure) is directly joined to dimensions (e.g. Time, Customer, Product, ...). It can be a constellation if you have multiple fact tables sharing the same conformed dimensions. You can also have some kind of normalisation where for instance the State and the Country would be in another dimension joined to the Customer dimension, but some reporting tools behave better with a pure Star Schema.


I wouldn't use a case statement.  Just use boolean logic

select *
  from table
 where (@duration = 0)
    or (@duration = 1 and duration > 0)
    or (@duration = 2 and duration = 0)


(From the screenshot) I see an unusual configuration where you have  SQL Server's Min and Max memory set the same. Leave min memory as default. See my answer to SQL Server Min Server Memory.
To determine ideal memory, refer - How to determine ideal memory for instance? and SQL Server Maximum and Minimum memory configuration
We have SSIS running packages on our production servers and they hardly cause any memory issues. I just leave enough memory for OS, since DTExec.exe is an external process to sqlserver.exe and so its memory requirements wont be included as part of MAX Memory. Also, SSIS is designed to process large amounts of data row by row in memory with high speed.
If you feel that there are SSIS packages that does run for hours, then I would suggest you to monitor below SSIS Performance Counters:
Buffers in use
Flat buffers in use
Private buffers in use
Buffers spooled
Rows read
Rows written

and from Top 10 SQL Server Integration Services Best Practices - SQL CAT Team

Process / Private Bytes (DTEXEC.exe): The amount of memory currently in use by Integration Services. This memory cannot be shared with other processes.
Process / Working Set (DTEXEC.exe): The total amount of allocated memory by Integration Services.

Old but still relevant : Integration Services: Performance Tuning Techniques


SQL geography::point null parameter error when parameter is not null?

I have struck similar issues with Geometry and Geography data types before.  I think the issue is to do with where (and how) the optimizer decides to build the geometry in the plan.

I was having an issue with insert statement that was basically

INSERT INTO (ID,GEOM)
SELECT ID, Geometry::Point(ISNULL(X,0),ISNULL(Y,0),0)
FROM HeapTable
WHERE X is not NULL and Y is not NULL

After playing around and getting a little confused found that

INSERT INTO (ID,GEOM)
SELECT ID, Geometry::Point(ISNULL(X,0),ISNULL(Y,0),0)
FROM HeapTable;

INSERT INTO (ID,GEOM)
SELECT ID, Geometry::Point(ISNULL(X,0),ISNULL(Y,0),0)
FROM HeapTable
WHERE ISNULL(X,0) != 0;

both work but the following doesn't

INSERT INTO (ID,GEOM)
SELECT ID, Geometry::Point(X,Y,0)
FROM HeapTable
WHERE ISNULL(X,0) != 0;

For the first query the plan shows that the compute scalar is done before the filter

And the expression being computed has had the ISNULL function stripped from it.

<ColumnReference Column="Expr1005" />
<ScalarOperator ScalarString="[Geometry]::Point(CONVERT_IMPLICIT(float(53),[sandbox].[dbo].[TestNullGeomIssueSource].[X],0),CONVERT_IMPLICIT(float(53),[sandbox].[dbo].[TestNullGeomIssueSource].[Y],0),(0))">

I think the Point on the Geometry and Geography is flawed in not allowing NULLs to be input.  Simply return a NULL as a result would be more correct in my opinion.

If you wish I can put up my entire test script for this.


Match similar special character in filters

The replace function will find a character in a string and replace it.
Use:
REPLACE(str, find_string, replace_with)

in your case:
where UPPER(REPLACE(city, 'Ì', 'I') like 'UPPER()%'

Hope this helps

http://www.w3resource.com/mysql/string-functions/mysql-replace-function.php


How do I CAST a date in a subquery? Currently getting an error: Conversion failed when converting date and/or time from character string

Use a CTE to get the wanted rows and then do the conversion
If the strings are not a correct format like seen below, you will get an error.

CREATE TABLE TPR(CaseNumber int,Source varchar(100))
GO

INSERT INTO TPR VALUES (1,'Q3 Completed'),(2,'2021/01/01')
GO

WITH CTE AS (
     SELECT Source
     FROM TPR
     WHERE Source NOT LIKE 'Q3 Completed' )
     SELECT CaseNumber
FROM TPR
WHERE Source IN (SELECT Source FROM CTE)
GO

| CaseNumber |
| ---------: |
|          2 |

db<>fiddle here


How to pass environment variables to a sql file from bash shell (PostgreSQL)

This will allow you to do it.  In your case, everything between '$$' is a "dollar-quoted string constant, so nothing gets interpreted.  See sql syntax, 4.1.2.4.
\set CREDO 'begin create user ' :dbuser '; exception when duplicate_object then raise notice ''user already exists''; end';
do :'CREDO';


A description describes an object like a product.

  Description: 5x10" widget used for squiggle placement.

A remark is often printed on invoices/statements/payments to show to the customer or supplier.

  Remark: This payment relates to invoices #1, #2, and #4.

A note calls out attention to something out of the ordinary or seperate to everything else. It may be internal-only.

  Note: Do not sell any more widgets to this customer because they have an outstanding invoice.

These aren't hard and fast rules but it's where I've seen them used.


How to preserve the original order of elements in an unnested array?

WITH ORDINALITY in Postgres 9.4 or later
The new feature simplifies this class of problems. The above query can now simply be:
SELECT *
FROM   regexp_split_to_table('I think Postgres is nifty', ' ') WITH ORDINALITY x(word, rn);
Or, applied to a table:
SELECT *
FROM   tbl t, regexp_split_to_table(t.my_column, ' ') WITH ORDINALITY x(word, rn);
Details:

PostgreSQL unnest() with element number

About the implicit LATERAL join:

What is the difference between LATERAL and a subquery in PostgreSQL?

Postgres 9.3 or older - and more general explanation
For a single string
You can apply the window function row_number() to remember the order of elements. However, with the usual row_number() OVER (ORDER BY col) you get numbers according to the sort order, not the original position in the string.
You could simply omit ORDER BY to get the position "as is":
SELECT *, row_number() OVER () AS rn
FROM   regexp_split_to_table('I think Postgres is nifty', ' ') AS x(word);
Performance of regexp_split_to_table() degrades with long strings.  unnest(string_to_array(...)) scales better:
SELECT *, row_number() OVER () AS rn
FROM   unnest(string_to_array('I think Postgres is nifty', ' ')) AS x(word);
However, while this normally works and I have never seen it break in simple queries, Postgres asserts nothing as to the order of rows without an explicit ORDER BY.
To guarantee ordinal numbers of elements in the original string, use generate_subscript() (improved with comment by @deszo):
SELECT arr[rn] AS word, rn
FROM   (
   SELECT *, generate_subscripts(arr, 1) AS rn
   FROM   string_to_array('I think Postgres is nifty', ' ') AS x(arr)
   ) y;
For a table of strings
Add PARTITION BY id to the OVER clause ...
Demo table:
CREATE TEMP TABLE strings(string text);
INSERT INTO strings VALUES
  ('I think Postgres is nifty')
 ,('And it keeps getting better');

I use ctid as ad-hoc substitute for a primary key. If you have one (or any unique column) use that instead.
SELECT *, row_number() OVER (PARTITION BY ctid) AS rn
FROM  (
   SELECT ctid, unnest(string_to_array(string, ' ')) AS word
   FROM   strings
   ) x;

This works without any distinct ID:
SELECT arr[rn] AS word, rn
FROM  (
   SELECT *, generate_subscripts(arr, 1) AS rn
   FROM  (
      SELECT string_to_array(string, ' ') AS arr
      FROM   strings
      ) x
   ) y;

SQL Fiddle.
Answer to question
SELECT z.arr, z.rn, z.word, d.meaning   -- , partofspeech -- ?
FROM  (
   SELECT *, arr[rn] AS word
   FROM  (
      SELECT *, generate_subscripts(arr, 1) AS rn
      FROM  (
         SELECT string_to_array(string, ' ') AS arr
         FROM   strings
         ) x
      ) y
   ) z
JOIN   dictionary d ON d.wordname = z.word
ORDER  BY z.arr, z.rn;


Select statement from 3 tables with join


.bat file works manually, but does nothing in SQL Server Agent job

The SQL Server user account has access to that specific folder?
Try running a simple xp_cmdshell to check that.

EXEC master.dbo.xp_cmdshell 'dir C:\<path>'


Get old database off external hard drive

When you say back up, do you mean a formatted back up as in the kind produced by pg_dump or an actual copy of the files in the pg_data folder?

If pg_dump, then you can restore using pg_restore or the in the UI using PGAdmin.

If pg_data folder, then you can install a new instance of Postgres and drop the pg_data folder from the old version in the same place where the new version is (but first stop the postmaster process and rename the new version of pg_data to pg_data_old first). As long as the major version numbers are the same, when you restart the postmaster process, it will read the data and log files and config files just fine (unless you have some special circumstances that I don't know about).


s is not valid
at that point you only have inserted

if you want the fields then try

OUTPUT inserted.ID, inserted.[Field1], inserted.[Field2]

instead of line 2 in your query.  You can find more here:

OUTPUT Clause (Transact-SQL)


Looks like your proxysql db doesn't have user proxysql_admin. Please use default proxysql user credentials (admin/admin).


How to save database connection information when using `psql`?

Thanks to the comment from @a_horse_with_no_name:
Enter the details in ~/.pg_service.conf, for example:
#comment
[myconnection]
host=localhost
port=5432
user=myuser
dbname=mydb
password=mypassword

Then set a PGSERVICE environment variable specifying which connection to use
echo PGSERVICE=myconnection

Then simply entering psql should make the connection.


a quick search in google gave me this:

Using JPA with App Engine
Quotas

Also, in the datastore you can store only entities, this means that when you save some user data, it will be stored in the Users entity with all the other users data, what you have to do is implement relationships between entities like in a regular database (MySQL, SQL Server, etc.), check it out here:

Using the Datastore with JDO


Select rows starting from specified value until another specified value

A suggestion that should work in 2008 version.

Tested at rextester.com:

with
  end_points as                       -- find start and end points
    ( select id, time, value
      from table_x
      where value in (15, 16)
    ),
  start_points as                     -- only the start points
    ( select id, time, value
      from end_points
      where value = 15
    )
select
    t.id, t.time, t.value
from
    start_points as s
  outer apply                         -- find where each island ends
    ( select top (1) ep.*
      from end_points as ep
      where s.id   = ep.id
        and s.time < ep.time
      order by ep.time
    ) as e
  cross apply                         -- and run through each island
    ( select p.id, p.time, p.value,
             rn = row_number() over (order by p.time)
      from table_x as p
      where s.id   = p.id
        and s.time <= p.time
        and         ( p.time < e.time
                   or p.time = e.time and e.value = 16
                   or          e.time is null)
    ) as t
where
    t.rn <= 100
order by
    t.id, t.time  ;

More info:

Using Common Table Expressions
Using APPLY
ROW_NUMBER (Transact-SQL)


Selecting an intersection with a fixed given percentage between two tables

NOT tested in Postgres, but it should be similar to:

SELECT V.ViewerID
FROM   Viewers AS V
GROUP BY V.ViewerID
HAVING CAST(COUNT(*) AS NUMERIC)
       >
       (0.7 * CAST((SELECT COUNT(*) FROM Serieses) AS NUMERIC))


You will have to export NLS_LANG env variable like mentioned here
http://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_eleven.htm#sthref1049
If you use expdp, you don't need to do this...


MySQL get values from the other table by using inner joins

Try this =>

SELECT DISTINCT (a.id_order_detail),(b.id_product),(b.name), img.id_image
        FROM ps_order_detail a join
        ps_product_lang b on a.product_id=b.id_product
        join ps_image img on img.id_product = b.id_product
where img.cover = 1
GROUP BY(name)


SQL Server CDC (Change Data Capture) Existing Data Seed

In case others end up here, a solution that works for me was to

store data in a temp table
delete source table data
delete cdc table data
insert temp table back into source table

I had to build up a list of column names in order to insert the IDENTITY which accounts for most of the script. Below is similar to what I used, however I was running inside a loop of all of my tables.

-- 1. Store data
SELECT * INTO #refreshTable FROM dbo.myTable;

-- 2. delete source table data
DELETE FROM dbo.myTable;

-- 3. delete cdc table data
DELETE FROM cdc.dbo_myTable_CT;

-- Build list of columns for insert
DECLARE @MyCursor CURSOR;
DECLARE @MyField nvarchar(max);
DECLARE @columns nvarchar(max) = '';
BEGIN
    SET @MyCursor = CURSOR FOR
        select [name] from tempdb.sys.columns where object_id = object_id('tempdb..#refreshTable');

    OPEN @MyCursor
    FETCH NEXT FROM @MyCursor
    INTO @MyField

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @columns = CONCAT(@columns, ', ', @MyField)
        FETCH NEXT FROM @MyCursor
        INTO @MyField
    END;

    CLOSE @MyCursor ;
    DEALLOCATE @MyCursor;
END;
SET @columns = SUBSTRING(@columns, 2, 10000); -- Not expecting long column names

-- 4. Insert data back into source table
SET IDENTITY_INSERT dbo.myTable ON;
DECLARE @sql nvarchar(MAX) = 'INSERT INTO dbo.myTable (' + @columns + ') SELECT * FROM #refreshTable';
EXEC (@sql);
SET IDENTITY_INSERT dbo.myTable OFF;


Do Oracle databases use Java internally?

No.

All (standard) packages are written in PL/SQL.
The DBMS engine itself is written in C

Edit:
Oracle does include a JVM which runs on the same machine as the database itself, but that is not used to run any "DBMS related" code.

It's only there to run stored procedures/functions written in Java.


Ordering of a dimension in sql server datawarehouse

Analysis Services will set the data type of the dimension attribute to be the same as the source column. It is sorted exactly as you asked for it. Your days are declared as a character type. Therefore, they will be sorted according to character sorting (1, 10, 11) and not numeric sorting (1,2,3).

You didn't specify whether this was multi-dimensional or tabular model but both will support a sort order property. In cases where I need to present character data, like month name, but need it sorted in month order, I will have columns like MonthName and MonthSortOrder and not present the SortOrder suffixed fields to the consuming tools.

In a multi-dimensional model, another option is to edit the Data Source View and replace the source table with a named query that includes a cast to integer for the "Day" column.


creating a table from a selection and then using group by - oracle sql

You need to add aliases for the computed columns so that Oracle knows what names the columns of the table should have

CREATE TABLE shipping_costs_grouped
AS
    SELECT SUM(invoice_amt) total_invoice_amt,
           SUM(entered_weight) total_entered_weight,
           SUM(billed_weight) total_billed_weight,
           SUM(net_amt) total_net_amt,
           MAX(ZONE) max_zone,
           pps_num
      FROM shipping_costs
     GROUP BY pps_num;

should be valid syntax.

Whether it is actually appropriate to create a table for this sort of thing is a question you'd have to answer.  My bias would be that it would be more appropriate to create a view or potentially a materialized view but I don't know the exact nature of the problem you're trying to solve.


Change expired user password on sql server from linux

There may be a better solution to this, but I often faced similar issues with Mac users whose passwords would expire, and due to lack of SSMS were unable to reset themselves.

I took to having a SQL Agent job to email any known non-Windows users of their password expiration 7 days out so they had plenty of time to issue the password reset themselves. This annoyingly requires keeping track of these users, but in a Microsoft shop it was manageable as it was only a few people.

In this case, if the account is already locked, ask your DBA to reset it and inquire about some sort of warning system like the above in the future. Or, like you said, find a willing Windows user to help you out.


Find out if two rows came from same transaction in Oracle

You can get the SCN for a row with the ORA_ROWSCN pseudocolumn

Unless you have set row-level dependency tracking for the table, this will report the SCN of the last change to the block the row is in, which may not be much use. You can turn on row-level dependency tracking at create table time only, so you may need to drop and re-create your table.

Note that row-level tracking increases the size of each row by 6 bytes.

with rowdependencies:

create table foo rowdependencies as
select level as id from dual connect by level<=10000;

select count(distinct ora_rowscn) from foo;

COUNT(DISTINCTORA_ROWSCN)
-------------------------
1

begin
  for r in (select id from foo) loop
    update foo set id = id where id=r.id;
    commit;
  end loop;
end;
/

select count(distinct ora_rowscn) from foo;

COUNT(DISTINCTORA_ROWSCN)
-------------------------
10000

without rowdependencies (norowdependencies is the default):

create table bar as
select level as id from dual connect by level<=10000;

select count(distinct ora_rowscn) from bar;

COUNT(DISTINCTORA_ROWSCN)
-------------------------
1

begin
  for r in (select id from bar) loop
    update bar set id = id where id=r.id;
    commit;
  end loop;
end;
/

select count(distinct ora_rowscn) from bar;

COUNT(DISTINCTORA_ROWSCN)
-------------------------
16


Alternatives to User Defined Performance Counters for CPU Monitoring - SQL 2014

I found a solution - the issue is I tried running perfom not locally to the server but from another server. Locally it works. (sigh)


The RDS Postgres instance has a parameter group. Check in RDS colsole what parameter group is defined: RDS -> Databases -> choose your db -> Configuration -> parameter group. If it's the default one, create a new one and change track_activity_query_size to the value you need. Apply the new parameter group or change the actual one. The parameter is static so a restart is needed in any case.

Check this link for more details, please:
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithParamGroups.html


MYSQL ERROR 2049 (HY000): Connection using old (pre-4.1.1) authentication protocol ref used (client option 'secure_auth' enabled)

On the command line, use something like the following, if you have no choice...

mysql -uTheUseerNAme -pThePassword DbName -h HostName --skip-secure-auth

Hope this helps someone, as this was my problem connecting from a Linux


If you add a filter on the right table, you effectively turn your outer join into an inner join.

Move the date filter from WHERE to ON.

Also I suggest an open-ended range rather than BETWEEN.

What do BETWEEN and the devil have in common?

Finally, I suggest not naming your CTE the same name as the base table (this is confusing), using proper schema references, and end with a semi-colon.

Bad habits : avoiding the schema prefix
Ladies and Gentlemen, Start Your Semi-Colons

Here's a start:

;With ec As
(
    Select
      StoreName
      ,DataFromOne = Count(ramalad)
    FROM dbo.DataFromOne
    WHERE EntryDate >= '20170101'
       AND EntryDate < '20170401'
   GROUP BY StoreName
)
Select
  ec.StoreName
  ,[Emp Count] = COUNT(fji.Employee)
  ,[PT Count] =      COALESCE(ec.DataFromOne,0)
FROM ec
LEFT OUTER JOIN dbo.DataFromTwo AS fji
   ON ec.StoreName = fji.StoreName
   AND fji.SaleDate >= '20170101'
   AND fji.SaleDate <  '20170401'
GROUP BY ec.StoreName, ec.DataFromOne
ORDER BY ec.StoreName;

In fact, you can simplify the query logic quite a bit (though I am not sure exactly how that will change your plan):

SELECT
  ec.StoreName,
  [Emp Count] = COUNT(fji.Employee),
  [PT Count]  = COUNT(ec.ramalad)
FROM dbo.DataFromOne AS ec
LEFT OUTER JOIN dbo.DataFromTwo AS fji
   ON ec.StoreName = fji.StoreName
   AND fji.SaleDate >= '20170101'
   AND fji.SaleDate <  '20170401'
WHERE ec.EntryDate >=  '20170101'
  AND ec.EntryDate <   '20170401'
GROUP BY ec.StoreName
ORDER BY ec.StoreName;


max_connections error during restoring PostgreSQL to Local Database

This error is caused by the large number of connections. To solve this problem, we recommend that you set the max_connections values of both the master and slave instances to 800 or less.

The max_connections values for the master and slave instances are required to be the same. For details, see Server Configuration and Server Setup and Operation on the official website of PostgreSQL.

Please Click here for detailed information.


If you want to replace the entire output of your current query with just one single-column row holding the total across the VarianceValue column of the original output, then just take the current query and remove

the GROUP BY clause,
the ORDER BY clause,
the left joins,
and all SELECT terms but one (the last one):

SELECT
  DEPARTMENT.DESCRIPTION AS DepartmentName,
  SUBDEPARTMENT.DESCRIPTION AS SubDeptName,
  SUBSUBDEPARTMENT.DESCRIPTION AS SubSubDeptName,
  sum(STOCKTAKELIST.QTYONHAND) Onhand,
  sum(STOCKTAKELIST.STOCKCOUNT) StockCount,
  sum(STOCKTAKELIST.STOCKCOUNT - STOCKTAKELIST.QTYONHAND) Variance,
  sum((STOCKTAKELIST.STOCKCOUNT - STOCKTAKELIST.QTYONHAND) * STOCKTAKELIST.CostPrice) VarianceValue
FROM
  STOCKTAKELIST STOCKTAKELIST
  INNER JOIN Stock STOCK ON STOCKTAKELIST.INTRECNO_STOCK = STOCK.INTRECNO
  INNER JOIN Location LOCATION ON STOCK.INTRECNO_LOCATION = LOCATION.INTRECNO
  LEFT JOIN DEPARTMENT ON STOCK.DEPTCODE = DEPARTMENT.DEPARTMENTCODE
  LEFT JOIN SUBDEPARTMENT ON STOCK.SUBDEPTCODE = SUBDEPARTMENT.SUBDEPTCODE AND SUBDEPARTMENT.DEPTCODE = DEPARTMENT.DEPARTMENTCODE
  LEFT JOIN SUBSUBDEPARTMENT ON STOCK.SUBSUBDEPTCODE = SUBSUBDEPARTMENT.SUBSUBDEPTCODE AND SUBSUBDEPARTMENT.SUBDEPTCODE = SUBDEPARTMENT.SUBDEPTCODE AND SUBSUBDEPARTMENT.DEPTCODE = DEPARTMENT.DEPARTMENTCODE
WHERE
  STOCKTAKELIST.ListCreate >= '01 Jun 2018' AND
  STOCKTAKELIST.ListCreate <= '10 Jun 2018 23:59:59' AND
  STOCKTAKELIST.IncludeInCount = 'Yes' AND
  STOCKTAKELIST.STOCKCOUNT <> STOCKTAKELIST.QTYONHAND AND
  STOCKTAKELIST.Status = 'Complete'
GROUP BY
  1,2,3
ORDER BY DEPARTMENT.DESCRIPTION, SUBDEPARTMENT.DESCRIPTION, SUBSUBDEPARTMENT.DESCRIPTION

That is, without the parts struck through the query should look like this (just to make it absolutely unambiguous and copy-paste-friendly):

SELECT
  sum((STOCKTAKELIST.STOCKCOUNT - STOCKTAKELIST.QTYONHAND) * STOCKTAKELIST.CostPrice) VarianceValue
FROM
  STOCKTAKELIST STOCKTAKELIST
  INNER JOIN Stock STOCK ON STOCKTAKELIST.INTRECNO_STOCK = STOCK.INTRECNO
  INNER JOIN Location LOCATION ON STOCK.INTRECNO_LOCATION = LOCATION.INTRECNO
WHERE
  STOCKTAKELIST.ListCreate >= '01 Jun 2018' AND
  STOCKTAKELIST.ListCreate <= '10 Jun 2018 23:59:59' AND
  STOCKTAKELIST.IncludeInCount = 'Yes' AND
  STOCKTAKELIST.STOCKCOUNT <> STOCKTAKELIST.QTYONHAND AND
  STOCKTAKELIST.Status = 'Complete';

It is quite possible that the inner joins are unnecessary too. They probably need to stay if they can affect the result. And they can affect the result if either of these is true:

STOCKTAKELIST.INTRECNO_STOCK is nullable and/or may contain values not found in STOCK.INTRECNO;
STOCK.INTRECNO_LOCATION is nullable and/or may contain values not found in LOCATION.INTRECNO.

If you have foreign key relationships in place, then what matters is just the nullability of STOCKTAKELIST.INTRECNO_STOCK and STOCK.INTRECNO_LOCATION. Now if both columns are not nullable and the referential integrity is guaranteed, then you should be able to get the same result by reducing the query to just this:

SELECT
  sum((STOCKCOUNT - QTYONHAND) * CostPrice) VarianceValue
FROM
  STOCKTAKELIST
WHERE
  ListCreate >= '01 Jun 2018' AND
  ListCreate <= '10 Jun 2018 23:59:59' AND
  IncludeInCount = 'Yes' AND
  STOCKCOUNT <> QTYONHAND AND
  Status = 'Complete';


How to determine if an SSRS data driven subscription job has completed

Using the ReportServer database, I have a script that appears to work.

Querying the same table as my data driven query

select count(distinct param)
from MyDataDrivenSourceTable

and compare the result of that to the below

    SELECT count(*)
  FROM [ReportServer].[dbo].[Schedule]
  INNER JOIN [ReportServer].[dbo].ReportSchedule ON ReportSchedule.ScheduleID = Schedule.ScheduleID
  INNER JOIN [ReportServer].[dbo].[ExecutionLogStorage] ON ExecutionLogStorage.ReportID = ReportSchedule.ReportID
  WHERE Schedule.ScheduleID = '********INSERT JOB NAME GUID HERE********'
  AND TimeStart >= schedule.LastRunTime

Then I should be able to compare the results to know when it has completed.

I'm relying on an internal table to SSRS which could change at any time, so I appreciate this method is totally unreliable, but it seems better than polling to see which files have appeared on disk.

This makes some massive assumptions:

The source table is not updated in-between the start of the SSRS subscription and when the check is performed.
The last run time is updated at the start of the execution.
I have no idea what would happen if the job was called again half way through the run.


PostgreSQL CREATE INDEX memory requirement

Through version 9.3, the indirection array used for sorting had to fit in a single 1GB memory allocation.  This created an artificial limit on the number of tuples which could be sorted in memory.  Once that limit was reached, it had to switch to a disk sort, even if there was memory left over.

This restriction was removed in version 9.4.


Role, whose name doesn't match database name, can't access the database

As it sounds, the tables (not necessary the database) is owned by the app user.  This is what the documentation tells about this:

  If the "Access privileges" column is empty for a given object, it means the object has default privileges (that is, its privileges column is null). Default privileges always include all privileges for the owner, and can include some privileges for PUBLIC depending on the object type, as explained above.

As nothing is displayed under "Access privileges" in the \dp output, and the app user can read from and write to the table, it is clear that it's the owner.  One can prove this, for example, by issuing \dt access_users, which should return something like

        List of relations
 Schema │ Name         │ Type  │ Owner
────────┼──────────────┼───────┼───────
 public │ access_users │ table │ app

This also means (not surprisingly), that no other user (except superusers and the members of the app role) has access to the tables.  If you want app2 to have some rights on them (and future tables), an earlier answer of mine might be interesting.


If you have no other code in the trigger that should be allowed to cancel the INSERT, then try the following:

Keep the XACT_ABORT OFF
Put the COMMIT TRAN back
Add a BEGIN TRAN at the end of the trigger

This should be the same issue as if you have an open transaction, exec a proc, and in that proc do a ROLLBACK (or even just a BEGIN TRAN and no COMMIT or ROLLBACK): it will error saying value of @@TRANCOUNT is different between entering and exiting the proc.


What is best practice to organize / normalize database tables?

Database refactoring is worth reading.

I think a sane procedure would follow these steps.

Audit
Analyze
Deprecate
Revoke
Remove
Kill

Audit

Determine which tables, views, stored procedures, etc., are still being used. Determine which applications are accessing your database.  There's more than one way to do that, and the best way depends in part on your dbms.

Auditing can hurt performance. This will take time; faster hardware might be justifiable.

Analyze

Analyze the results of your audit. Distinguish currently used database objects from seemingly unused database objects. There's no such thing as "definitely unused database objects"; you might have a table that's used only once a year for some kind of accounting process.

Deprecate

Decide which objects to end support for, and deprecate them. Communicate this change both widely and loudly.  Remain open to changing your mind when you find you've stepped on someone's toes.

Revoke

Revoke permissions on formerly deprecated database objects. If you've overlooked an application or user, they'll eventually complain. You might want to approach this in several steps, spread out over time.

Remove

Remove the formerly deprecated database objects in such a way you can easily replace them.

Kill

Finally, kill the unused database objects. Breathe easier.

Each step takes time. Allow more time than you think you need.


SQL Server - How to grant SELECT permission on View that access data in schemas from differents owners?

Based on one of the suggestions that Dan Guzman gave me in your answer, and after read this question I found a workaround to my problem.

Apparently, there is no way, in scenario as described, to grant to userX SELECT permission in the viewABC without grant also SELECT permission on tableA.

The solution that I found was the following:

in schemaD I created a Table-Valued Function that return the same record set of viewABC - despite ownerX isn't owner of schemaA, he has SELECT permission on tableA
the EXECUTE AS clause was used to guarantee that any execution of the function will use ownerX permissions - in this context doesn't matter the permission that userX has on tableA
to userX was granted SELECT permission in the created function - not EXECUTE permission, since the function return a table

The Table-Valued Function sample code:

CREATE FUNCTION schemaD.udfABC ()
RETURNS @tabABC TABLE (
  fieldA INT NOT NULL, fieldB INT NOT NULL, fieldC INT NOT NULL
)
WITH EXECUTE AS OWNER
AS
BEGIN
  INSERT INTO @tabABC (fieldA, fieldB, fieldC)
    SELECT a.fieldA, b.fieldB, c.fieldC
      FROM schemaA.tableA a
     INNER JOIN schemaB.tableB b ON a.id = b.idA
     INNER JOIN schemaC.tableC c ON b.id = c.idB;

  RETURN;
END

Granting SELECT permission:

GRANT SELECT ON schemaD.udfABC TO userX;

Now userX can use this command to get data:

SELECT * FROM schemaD.udfABC();


How to change password of user in physical standby database Oracle 11g?

You do realize the standby is a "physical" copy of the primary (read-only in this case)? If you change the password in the primary, the changes will be propagated to the standby also. You can not change such things in a physical standby independently.


EDIT:

To change the mode

This can be done in two ways:

Open your "my.ini" file within the MySQL installation directory, and look for the text "sql-mode".
Find:

Code:

Set the SQL mode to strict

sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
Replace with:

Code:

Set the SQL mode to strict

sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
Or

You can run an SQL query within your database management tool, such as phpMyAdmin:
Code:

SET @@global.sql_mode= '';

From post:https://stackoverflow.com/questions/15949038/error-code-1406-data-too-long-for-column-mysql


your query should work in mysql

CREATE TABLE notification (
  `id` INTEGER,
  `sendto_type` VARCHAR(11),
  `sendto_id` INTEGER
);

INSERT INTO notification
  (`id`, `sendto_type`, `sendto_id`)
VALUES
  ('1', 'App\User', '1'),
  ('2', 'App\User', '2'),
  ('3', 'App\Station', '1'),
  ('4', 'App\Station', '2');

CREATE TABLE user (
  `id` INTEGER,
  `firstname` VARCHAR(6),
  `lastname` VARCHAR(8)
);

INSERT INTO user
  (`id`, `firstname`, `lastname`)
VALUES
  ('1', 'dummy', 'imdummy'),
  ('2', 'dummy2', 'imdummy2'),
  ('3', 'dummy3', 'imdummy4'),
  ('4', 'dummy4', 'imdummy3');

SELECT n.id,u.* FROM notification n inner JOIN user u ON  u.id = n.sendto_id WHERE `sendto_type` = "App\User"

id | id | firstname | lastname
-: | -: | :-------- | :-------
 1 |  1 | dummy     | imdummy
 2 |  2 | dummy2    | imdummy2

db<>fiddle here


Find users having grants on a table in MySQL

This messy query should give you every user that can access product_db.product_events
SELECT user,host,'Global' Level FROM mysql.user WHERE
select_priv='Y' OR insert_priv='Y' OR update_priv='Y' OR delete_priv='Y'
UNION
SELECT user,host,'DB' FROM mysql.db
WHERE db='product_db' AND
(select_priv='Y' OR insert_priv='Y' OR update_priv='Y' OR delete_priv='Y')
UNION
SELECT user,host,'Table' FROM mysql.tables_priv
WHERE db='product_db' AND table_name='product_events';

If you are looking for users with all four(4) permissions (SELECT,INSERT,UPDATE and DELETE grants), then use this (basically using AND instead of OR)
SELECT user,host,'Global' Level FROM mysql.user WHERE
select_priv='Y' AND insert_priv='Y' AND update_priv='Y' AND delete_priv='Y'
UNION
SELECT user,host,'DB' FROM mysql.db
WHERE db='product_db' AND
(select_priv='Y' AND insert_priv='Y' AND update_priv='Y' AND delete_priv='Y')
UNION
SELECT user,host,'Table' FROM mysql.tables_priv
WHERE db='product_db' AND table_name='product_events';

NOTE : Table level privileges are even more messier to check since the grants are listed as a set rather than as individual columns
mysql> desc mysql.tables_priv;
+-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------+
| Field       | Type                                                                                | Null | Key | Default           | Extra                       |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------+
| Host        | char(60)                                                                                | NO   | PRI |                   |                             |
| Db          | char(64)                                                                                | NO   | PRI |                   |                             |
| User        | char(16)                                                                                | NO   | PRI |                   |                             |
| Table_name  | char(64)                                                                                | NO   | PRI |                   |                             |
| Grantor     | char(77)                                                                                | NO   | MUL |                   |                             |
| Timestamp   | timestamp                                                                                | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| Table_priv  | set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') | NO   |     |                   |                             |
| Column_priv | set('Select','Insert','Update','References')                                                                                | NO   |     |                   |                             |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------+
8 rows in set (0.00 sec)

mysql>


Here's one method of getting running totals after reset. There are probably others out there. Here is your test data:

CREATE TABLE user20907 (
ID INTEGER NULL,
CUSTOMERID INTEGER NULL,
AMOUNT INTEGER NULL,
RESETYN INTEGER NULL
);

BEGIN TRANSACTION;
INSERT INTO user20907 VALUES (1,111,5,0);
INSERT INTO user20907 VALUES (2,111,6,0);
INSERT INTO user20907 VALUES (3,111,7,1);
INSERT INTO user20907 VALUES (4,111,8,0);
INSERT INTO user20907 VALUES (5,111,9,0);
INSERT INTO user20907 VALUES (6,111,4,1);
INSERT INTO user20907 VALUES (7,111,7,0);
INSERT INTO user20907 VALUES (8,111,9,0);
COMMIT TRANSACTION;

First get a running total of the ResetYN column. I'm going to call it SUM_OF_RESETYN. In an outer query you can partition by both CustomerID and SUM_OF_RESETYN. For the reset running total, instead of taking Amount directly you can do a case statement similar to what you already have:

SELECT
      t.ID
    , CUSTOMERID
    , AMOUNT
    , RESETYN
    , RUNNINGTOTAL
    , SUM(CASE WHEN RESETYN = 1 THEN 0 ELSE AMOUNT END) OVER (PARTITION BY CUSTOMERID, SUM_OF_RESETYN ORDER BY ID) RESETRUNNINGTOTAL
FROM
(
    SELECT
          ID
        , CUSTOMERID
        , AMOUNT
        , RESETYN
        , SUM(AMOUNT) OVER (PARTITION BY CUSTOMERID ORDER BY ID) RUNNINGTOTAL
        , SUM(RESETYN) OVER (PARTITION BY CUSTOMERID ORDER BY ID) SUM_OF_RESETYN
    FROM user20907
) t;

I ran that query and the results matched yours, except that I did not have a NULL value for RESETRUNNINGTOTAL for ID 1. Was that intentional? If so it should be easy to integrate that bit of logic into my query.


Sending a mail using DB2's Proceude UTL_MAIL

Based on what you are trying to do you would want to set up the STMP server at the database level rather than the DAS level. You set it up at the DAS level if you want DB2 to email alerts to you. See the Information Center here.

You set it up at the database level if you want to use the UTL_MAIL module as you are wishing to use.

You would need to do the following:

db2 connect to mydb -- or whatever your database name is.
db2 update db cfg using smtp_server '<server or ip>:<port>'
-- you can have a list of smtp servers if you want, just use a comma between them.
db2 connect reset

This setting takes affect immediately. Please see Information Center for more details.


How oracle syncs data across nodes in a cluster

Well actually….no. The second Development Milestone Release of MySQL Cluster 7.2 introduces support for what we call “Multi-Site Clustering”. In this post, I’ll provide an overview of this new capability, and considerations you need to make when considering it as a deployment option to scale geographically dispersed database services.

You can read more about MySQL Cluster 7.2.1 in the article posted on the MySQL Developer Zone.

MySQL Cluster has long offered Geographic Replication, distributing clusters to remote data centers to reduce the affects of geographic latency by pushing data closer to the user, as well as providing a capability for disaster recovery.

Multi-Site Clustering provides a new option for cross data center scalability. For the first time splitting data nodes across data centers is a supported deployment option. With this deployment model, users can synchronously replicate updates between data centers without needing to modify their application or schema for conflict handling, and automatically failover between those sites in the event of a node failure.

MySQL Cluster offers high availability by maintaining a configurable number of data replicas.  All replicas are synchronously maintained by a built-in 2 phase commit protocol.  Data node and communication failures are detected and handled automatically.  On recovery, data nodes automatically rejoin the cluster, synchronize with running nodes, and resume service.

All replicas of a given row are stored in a set of data nodes known as a nodegroup.  To provide service, a cluster must have at least one data node from each nodegroup available at all times.  When the cluster detects that the last node in a nodegroup has failed, the remaining cluster nodes will be gracefully shutdown, to ensure the consistency of the stored databases on recovery.

Improvements to the heartbeating mechanism used by MySQL Cluster enables greater resilience to temporary latency spikes on a WAN, thereby maintaining operation of the cluster. A new “ConnectivityCheck” mechanism is introduced, which must be explicitly configured. This extra mechanism adds messaging overheads and failure handling latency, and so is not switched on by default.

When configuring Multi-Site clustering, the following factors must be considered:

Bandwidth
Low bandwidth between data nodes can slow data node recovery.  In normal operation, the available bandwidth can limit the maximum system throughput.  If link saturation causes latency on individual links to increase, then node failures, and potentially cluster failure could occur.

Latency and performance
Synchronously committing transactions over a wide area increases the latency of operation execution and commit, therefore individual operations are slowed. To maintain the same overall throughput, higher client concurrency is required.  With the same client concurrency level, throughput will decrease relative to a lower latency configuration.

Latency and stability
Synchronous operation implies that clients wait to hear of the success or failure of each operation before continuing. Loss of communication to a node, and high latency communication to a node are indistinguishable in some cases.  To ensure availability, the Cluster monitors inter-node communication.  If a node experiences high communication latency, then it may be killed by another node, to prevent its high latency causing service loss.

Where inter-node latencies fluctuate, and are in the same range as the node-latency-monitoring trigger levels, node failures can result.  Node failures are expensive to recover from, and endanger Cluster availability.

To avoid node failures, either the latency should be reduced, or the trigger levels should be raised.  Raising trigger levels can result in a longer time-to-detection of communication problems.

WAN latencies
Latency on an IP WAN may be a function of physical distance, routing hops, protocol layering, link failover times and rerouting times. The maximum expected latency on a link should be characterized as input to the cluster configuration.

Survivability of node failures
MySQL Cluster uses a fail fast mechanism to minimize time-to-recovery. Nodes that are suspected of being unreachable or dead are quickly excluded from the Cluster.  This mechanism is simple and fast, but sometimes takes steps that result in unnecessary cluster failure.  For this reason, latency trigger levels should be configured a safe margin
above the maximum latency variation on inter-data node links.

Users can configure various MySQL Cluster parameters including heartbeats, Connectivity_Check, GCP timeouts and transaction deadlock timeouts. You can read more about these parameters in the documentation.

Recommendations for Multi-Site Clustering
- Ensure minimal, stable latency;
- Provision the network with sufficient bandwidth for the expected peak load - test with node recovery and system recovery;
- Configure the heartbeat period to ensure a safe margin above latency fluctuations;

Configure the ConnectivtyCheckPeriod to avoid unnecessary node failures;
Configure other timeouts accordingly including the GCP timeout, transaction deadlock timeout, and transaction inactivity timeout.

Example
The following is a recommendation of latency and bandwidth requirements for applications with high throughput and fast failure detection requirements:
- latency between remote data nodes must not exceed 20 milliseconds;
- bandwidth of the network link must be more than 1 Gigabit per Second.

For applications that do not require this type of stringent operating environment, latency and bandwidth can be relaxed, subject to the testing recommended above.

As the recommendations demonstrate, there are a number of factors that need to be considered before deploying multi-site clustering. For geo-redundancy, Oracle recommends Geographic Replication, but multi-site clustering does present an alternative deployment, subject to the considerations and constraints discussed above.


Log statements from within triggers in Postgresql

The auto_explain module has auto_explain.log_nested_statements since at least 8.4, so that's one option. It's a bit heavyweight but I'm not aware of any other logging option that will record statements run inside PL/PgSQL functions and triggers.

See this similar question.


Datetime Difference in MySQL when format is Varchar

You really need to unify all Date and Time Stamps into one format for both start and stop.

Just to give you an example, I'll compute the time difference for the first row.

Here is the code to compute it:

SET @StartDT = 'Mon Jan 21, 21:07:47 IST 2013';
SET @StopDT  = 'Tue Jan 22, 07:59:58';
SET @YYYY_Start = SUBSTR(@StartDT,-4);
SET @MMM_Start  = RIGHT(LEFT(@StartDT,7),3);
SET @DD_Start   = RIGHT(LEFT(@StartDT,10),2);
SET @HMS_Start  = RIGHT(LEFT(@StartDT,20),8);
SET @YYYY_Stop  = SUBSTR(@StartDT,-4);
SET @MMM_Stop   = RIGHT(LEFT(@StopDT,7),3);
SET @DD_Stop    = RIGHT(LEFT(@StopDT,10),2);
SET @HMS_Stop   = RIGHT(LEFT(@StopDT,20),8);
SET @MM_Start   = RIGHT(LEFT((LOCATE(@MMM_Start,'JanFebMarAprMayJunJulAugSepOctNovDev')+2)/3+100,3),2);
SET @MM_Stop    = RIGHT(LEFT((LOCATE(@MMM_Stop,'JanFebMarAprMayJunJulAugSepOctNovDev')+2)/3+100,3),2);
SELECT @YYYY_Start,@YYYY_Stop,@MM_Start,@MM_Stop,@DD_Start,@DD_Stop,@HMS_Start,@HMS_Stop,@MM_Start,@MM_Stop;
SET @YYYMMDD_Start = CONCAT(@YYYY_Start,'-',@MM_Start,'-',@DD_Start);
SET @YYYMMDD_Stop  = CONCAT(@YYYY_Stop,'-',@MM_Stop,'-',@DD_Stop);
SET @TS_Start = CONCAT(@YYYMMDD_Start,' ',@HMS_Start);
SET @TS_Stop  = CONCAT(@YYYMMDD_Stop,' ',@HMS_Stop);
SET @TimeDiffSec = UNIX_TIMESTAMP(@TS_Stop) - UNIX_TIMESTAMP(@TS_Start);
SET @TimeDiff = SEC_TO_TIME(@TimeDiffSec);
SELECT @TS_Start,@TS_Stop,@TimeDiffSec,@TimeDiff;

Ready to see the difference? Here it is:

mysql> SET @StartDT = 'Mon Jan 21, 21:07:47 IST 2013';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @StopDT  = 'Tue Jan 22, 07:59:58';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @YYYY_Start = SUBSTR(@StartDT,-4);
Query OK, 0 rows affected (0.00 sec)

mysql> SET @MMM_Start  = RIGHT(LEFT(@StartDT,7),3);
Query OK, 0 rows affected (0.00 sec)

mysql> SET @DD_Start   = RIGHT(LEFT(@StartDT,10),2);
Query OK, 0 rows affected (0.00 sec)

mysql> SET @HMS_Start  = RIGHT(LEFT(@StartDT,20),8);
Query OK, 0 rows affected (0.00 sec)

mysql> SET @YYYY_Stop  = SUBSTR(@StartDT,-4);
Query OK, 0 rows affected (0.00 sec)

mysql> SET @MMM_Stop   = RIGHT(LEFT(@StopDT,7),3);
Query OK, 0 rows affected (0.00 sec)

mysql> SET @DD_Stop    = RIGHT(LEFT(@StopDT,10),2);
Query OK, 0 rows affected (0.00 sec)

mysql> SET @HMS_Stop   = RIGHT(LEFT(@StopDT,20),8);
Query OK, 0 rows affected (0.00 sec)

mysql> SET @MM_Start   = RIGHT(LEFT((LOCATE(@MMM_Start,'JanFebMarAprMayJunJulAugSepOctNovDev')+2)/3+100,3),2);
Query OK, 0 rows affected (0.00 sec)

mysql> SET @MM_Stop    = RIGHT(LEFT((LOCATE(@MMM_Stop,'JanFebMarAprMayJunJulAugSepOctNovDev')+2)/3+100,3),2);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @YYYY_Start,@YYYY_Stop,@MM_Start,@MM_Stop,@DD_Start,@DD_Stop,@HMS_Start,@HMS_Stop,@MM_Start,@MM_Stop;
+-------------+------------+-----------+----------+-----------+----------+------------+-----------+-----------+----------+
| @YYYY_Start | @YYYY_Stop | @MM_Start | @MM_Stop | @DD_Start | @DD_Stop | @HMS_Start | @HMS_Stop | @MM_Start | @MM_Stop |
+-------------+------------+-----------+----------+-----------+----------+------------+-----------+-----------+----------+
| 2013        | 2013       | 01        | 01       | 21        | 22       | 21:07:47   | 07:59:58  | 01        | 01       |
+-------------+------------+-----------+----------+-----------+----------+------------+-----------+-----------+----------+
1 row in set (0.00 sec)

mysql> SET @YYYMMDD_Start = CONCAT(@YYYY_Start,'-',@MM_Start,'-',@DD_Start);
Query OK, 0 rows affected (0.00 sec)

mysql> SET @YYYMMDD_Stop  = CONCAT(@YYYY_Stop,'-',@MM_Stop,'-',@DD_Stop);
Query OK, 0 rows affected (0.00 sec)

mysql> SET @TS_Start = CONCAT(@YYYMMDD_Start,' ',@HMS_Start);
Query OK, 0 rows affected (0.00 sec)

mysql> SET @TS_Stop  = CONCAT(@YYYMMDD_Stop,' ',@HMS_Stop);
Query OK, 0 rows affected (0.00 sec)

mysql> SET @TimeDiffSec = UNIX_TIMESTAMP(@TS_Stop) - UNIX_TIMESTAMP(@TS_Start);
Query OK, 0 rows affected (0.00 sec)

mysql> SET @TimeDiff = SEC_TO_TIME(@TimeDiffSec);
Query OK, 0 rows affected (0.00 sec)

Final Answer

mysql> SELECT @TS_Start,@TS_Stop,@TimeDiffSec,@TimeDiff;
+---------------------+---------------------+--------------+-----------+
| @TS_Start           | @TS_Stop            | @TimeDiffSec | @TimeDiff |
+---------------------+---------------------+--------------+-----------+
| 2013-01-21 21:07:47 | 2013-01-22 07:59:58 |        39131 | 10:52:11  |
+---------------------+---------------------+--------------+-----------+
1 row in set (0.00 sec)

Even though this is right, it would be illogical to script this convoluted method for all rows, especially since some rows have different formats.

Therefore, it is in your best interest to convert start and stop columns into DATETIME or TIMESTAMP


Casting a string of 000000000000017943 to decimal(18,2) is going to result in a value of 17943 The source doesn't have a decimal place, so why would the cast know you need a decimal there?

Also, given your source data, your substring formula is off. It's resulting in a source string of R00000000000001794

That said, I take one of 2 approaches. Either I use a string operation to insert the decimal place or I use math to divide by 100. If I'm already dealing with strings, I usually stick with that approach as I hate to keep casting values about.

Off to lunch, but here is the Biml I used to generate the package. Grab bidshelper and use that to generate the same SSIS package as me.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <FileFormats>
        <FlatFileFormat Name="FFF" IsUnicode="false" FlatFileType="Delimited">
            <Columns>
                <Column Name="EntireRow" DataType="AnsiString" Delimiter="CRLF" Length="58" />
            </Columns>
        </FlatFileFormat>
    </FileFormats>
    <Connections>
        <FlatFileConnection FilePath="C:\ssisdata\dba_89464.txt" FileFormat="FFF" Name="CM_FF" />
    </Connections>
    <Packages>
        <Package ConstraintMode="Linear" Name="dba_89464">
            <Tasks>
                <Dataflow Name="DFT Source">
                    <Transformations>
                        <FlatFileSource ConnectionName="CM_FF" Name="FF Source" />
                        <DerivedColumns Name="DER Split to row type">
                            <Columns>
                                <Column DataType="AnsiString" Name="Header" Length="2">SUBSTRING([EntireRow],1,2)</Column>
                            </Columns>
                        </DerivedColumns>
                        <ConditionalSplit Name="CSPL - Split on Header">
                            <OutputPaths>
                                <OutputPath Name="Type 2">
                                    <Expression>[Header] == "02"</Expression>
                                </OutputPath>
                            </OutputPaths>
                        </ConditionalSplit>
                        <Multicast Name="MC Demo approaches">
                            <InputPath OutputPathName="CSPL - Split on Header.Type 2" />
                            <OutputPaths>
                                <OutputPath Name="String Approach" />
                                <OutputPath Name="Math Approach" />
                            </OutputPaths>
                        </Multicast>
                        <!--
                        Generate our value as string and one without the decimal place but of a numeric type
                        -->
                        <DerivedColumns Name="DER Type 2 processing">
                            <InputPath OutputPathName="MC Demo approaches.String Approach" />
                            <Columns>
                                <Column DataType="AnsiString" Name="strTemporaryMyValue" Length="18">TRIM(SUBSTRING(EntireRow,11,18))</Column>
                            </Columns>
                        </DerivedColumns>
                        <!--
                            Add our decimal place, string operations
                            This is not concerned with empty strings, etc
                        -->
                        <DerivedColumns Name="DER Add decimal place">
                            <Columns>
                                <Column DataType="AnsiString" Name="withDecimalPlace"  Length="19">SUBSTRING([strTemporaryMyValue], 1, LEN([strTemporaryMyValue]) -2) + "." + RIGHT([strTemporaryMyValue], 2)</Column>
                            </Columns>
                        </DerivedColumns>

                        <DerivedColumns Name="DER Make string to decimal">
                            <Columns>
                                <Column DataType="Decimal" Name="MyValueViaString" Precision="18" Scale="2">(DT_NUMERIC, 18, 2)[withDecimalPlace]</Column>
                            </Columns>
                        </DerivedColumns>

                        <DerivedColumns Name="DER Dataviewer connector String" />

                        <DerivedColumns Name="DER slice string into numeric type">
                            <InputPath OutputPathName="MC Demo approaches.Math Approach" />
                            <Columns>
                                <Column DataType="Decimal" Name="decTemporaryMyValue" Precision="20" Scale="0">(DT_NUMERIC, 20, 0) TRIM(SUBSTRING(EntireRow,11,18))</Column>
                            </Columns>
                        </DerivedColumns>
                        <!--
                            Behold, the power of math
                        -->
                        <DerivedColumns Name="DER Divide approach">
                            <Columns>
                                <Column DataType="Decimal" Name="MyValue" Precision="18" Scale="2">(DT_NUMERIC, 18, 2)decTemporaryMyValue/100.0</Column>
                            </Columns>
                        </DerivedColumns>

                        <DerivedColumns Name="DER Dataviewer connector Math" />
                    </Transformations>
                </Dataflow>
            </Tasks>
        </Package>
    </Packages>
</Biml>


How to reset session context/variables in JDBC pooled connections - MySQL / Oracle

DBMS_SESSION.CLEAR_ALL_CONTEXT

This procedure clears application context information in the specified
namespace.

DBMS_SESSION.RESET_PACKAGE

This procedure de-instantiates all packages in this session. It frees
the package state.


How to clean up duplicate objects in SYS/SYSTEM

It seems you have run one of cat*.sql scripts as SYSTEM instead of SYS. Not cool.

Alternatives:

Contact support.
...or determine what exactly has been done, and then having the SQL script(s) revert the actions one by one. There is nothing in Oracle to help you, you need to determine by your own means (some old notes? old screenlogs? clues in old .bash_history?).
...or schedule downtime, expdp everything what is relevant in the entire database, re-create, impdp (of course don't impdp SYSTEM schema).
...or if you are unable to proceed with either 1, 2, 3, don't perform DROPs by any heuristics/guesswork. Continue as you are. Nothing really wrong with your database - just some extra objects in SYSTEM (not in SYS, I hope). They could stand in a way of some future action, but they might not, nobody knows. Normally perform cold backup before upgrade. Chances are the upgrade will be successful. Schedule steps 1,2,3 for later.


Database design - model a 'Company' with 'Owner', 'Partner', 'Employee'

I would not have separate tables but you might want to consider not using enum for the capacity column.

Instead use a table named, perhaps, capacity that has an id referenced in the personnel table (much like user ID and company ID). That makes it trivial to add new titles/positions without disturbing anything else.


MySQL asynchronous multi master replication conflict resolution

Conflict-free replication isn't supported in multi-source replication...

You should look at cluster multi-master setup, but, I don't know a multi-master cluster with lazy updates (async-replication).


MSDB - Backup information is not updating

As you are running SQL Server 2012 it might be this bug.
Check also this article: "Could not insert a backup or restore history/detail record in the msdb database" While restoring msdb