texts: It is possible to extract Extented Properties using SQL Developer Data Modeler?

texts:
I'm using SQL Developer Data Modeler in a oracle server. No problem, I can connect to SQL Server, list tables and generate a really good report. But what I need is to extract the extended properties  from tables and columns. I'm looking all over the internet but what I found is:

MS SQL Server
Support for following objects – database, login, user and roles with their privileges and permissions, default, rule, user-defined type, computed and identity columns, partitioned tables using partition function and partition scheme, primary and secondary XML indexes, triggers on tables and views, stored procedures, indexes on views, synonyms
    Domains can be created as user-defined types
    **Extended properties for each object can be defined and generated in DDL**
    Database and schema controlled generation of DDL

http://www.oracle.com/technetwork/developer-tools/datamodeler/featurelist-167684.html

From the official manual. But I don't want to generate manually the properties with the software. I  have already all properties of extended properties from almost all tables.

Or can I make a Data Dictionary with my good SQL Server Management Studio? I can only see reports from disk usage, performance metrics and etc.

This is what I have for now:


texts: How to update data with a trigger function and a join

texts:
I have two relations :

CREATE TABLE relation_a (id_a int PRIMARY KEY, field_1 int);
CREATE TABLE relation_b (id_b int PRIMARY KEY,fk_a int REFERENCES relation_a(id_a), field_1 int);
INSERT INTO relation_a VALUES (1,100), (2,101), (3,102);
INSERT INTO relation_b VALUES (1,1), (2,2), (3,3)

I want to create a trigger which always set :

SET b.field_1 = a.field_1 FROM relation_a a, relation_b b WHERE b.fk_a = a.id_a

I tried something like this :

CREATE OR REPLACE FUNCTION function1() RETURNS trigger AS
$$
BEGIN
    UPDATE relation_b as b
    SET field_1 = a.field_1
    FROM relation_a a
    WHERE a.id_a = new.fk_a;
END
$$
LANGUAGE PLPGSQL;

CREATE TRIGGER trigger1
   AFTER INSERT OR UPDATE
   ON relation_b
   FOR EACH ROW EXECUTE PROCEDURE function1();

If i try to insert or update data in relation_b, i got a very long error message i can't understand :

Can someone explain me the right way ? Thanks a lot


texts: Can I do a backup without extension functions?

texts:
I use the PostGIS extension on my database. The thing is when I do a backup and then a restore, all the PostGIS functions give a warning during the restore process, because the functions already exist, as the extension is already installed.

I have to scroll through all the warning to make sure there isn't a real error.

So what is the right way to backup / restore, so I can have a clean restore process?


texts: Is there any performance benefit to minifying MySQL queries?

texts: I recently ran across some tools that help you minify queries, and I'm curious to know if that's actually something that could help performance.


texts: fetch elements between bounds in postgis

texts:
I have a table 'locations' in postgresql:

name (character varying(24))
geography (geography)

I insert data like this:
INSERT INTO locations (name, geography) VALUES ($1, ST.MakePoint($2,$3)::geography)
// $1 = name of location
// $2 = longitude (float)
// $3 = latitude (float)

This works as expected.
If I have a lat/lng point and I want to get the the distance of all locations to this point, I do this:
SELECT name, ST_Distance(geography, ST.MakePoint($1,$2)::geography) as distance FROM locations
// $1 = longitude (float)
// $2 = latitude (float)

Now, I want to select all locations between boundaries. I have a NE and SW lat/lng point, but I have no clue how to query my table against this.


texts: Is there any way we can figure out what all indexes are cached into RAM ? I want to know page related stats for individual indexes (# of hits and miss )


texts: MySQL Workbench - store a query to apply to various schemas

texts:
We have multiple identical schemas. How can I store a query and easily apply it to any of multiple schemas? Bonus points for a drag & drop or point & click method of executing the query.

My first attempt was to set a variable for the schema, like this

SET @db = MySchema;

SELECT * from @db.mytable;

but I get Error Code: 1146. Table 'db.tagdescriptions' doesn't exist.

I'm using MySQL Workbench 6.3, connecting to MySQL Community Server (GPL) 5.6.19-log on Win64.


texts: A "best practice" Postgresql function template for ETL processes

texts:
I use SQL Server in my day job, and have a personal project to build a star schema data warehouse in Postgres 9.4 on Ubuntu 14.04 to extend my knowledge (it'll be approximately 300GB uncompressed).

I'll be using ELT as opposed to ETL and managing all this via code. Bash and cron will be used for managing jobs via psql (I'm aware of pgAgent), and I wish to ensure any logging of exceptions is accurate.

I've been trying to find some "best practice" function templates for Postgres that specifically has a generic exception handling pattern for batch loading of databases, so far without much luck, as most I've come across are wanting to deal with custom exceptions, or re-throwing/raising the exception.

For my purposes I don't want the function to error back to the calling process, the logging is enough to determine states, as all dependencies will be managed through this logging workflow.

I've done the following:

read the manual on error trapping
found the example provided for capturing exceptions
used the example here for setting functions variables
found a couple of examples, one which raises exceptions, the other that wraps another begin ... end inside the function definition, that didn't seem to quite be what I was looking for
and that I should prefix local variables so as not to confuse the parser

What I've come up with as a basic pattern so far is:

create or replace function
    table_schema.load_table_name(l_job_date date)
returns
    void as $$
declare
    -- control variables
    l_job_code              constant varchar(50) = 'table_schema.load_table_name';
    l_job_start             timestamp;
    l_job_end               timestamp;

    -- exception management variables
    l_exception_error_code  text;
    l_exception_message     text;
    l_exception_detail      text;
    l_exception_hint        text;
    l_exception_context     text;
begin
    -- capture start
    l_job_start = clock_timestamp();

    -- do stuff here
    -- ...
    -- ...
    -- ...

    -- capture end
    l_job_end =  = clock_timestamp();

    -- log success
    perform fn_log_job(
        l_job_code
        ,l_job_date
        ,l_job_start
        ,l_job_end
        ,'success'
    )
    ;

-- if something "breaks" do the following
exception when others then
    get stacked diagnostics
        l_exception_error_code  = RETURNED_SQLSTATE
        ,l_exception_message        = MESSAGE_TEXT
        ,l_exception_detail     = PG_EXCEPTION_DETAIL
        ,l_exception_hint           = PG_EXCEPTION_HINT
        ,l_exception_context        = PG_EXCEPTION_CONTEXT
    ;

    -- log exception for debugging
    perform fn_log_exception(
        l_exception_error_code
        ,l_exception_message
        ,l_exception_detail
        ,l_exception_hint
        ,l_exception_context
    )
    ;

    -- log job failure
    perform fn_log_job(
        l_job_code
        ,l_job_date
        ,l_job_start
        ,l_job_end
        ,'fail'
    )
    ;

end;
$$ language plpgsql;

Is this all that is needed? (or have I done anything incorrectly?)

I have nothing in there yet about explicit transaction management as I'm still learning how these are handled in Postgres in general, and in function calls, any tips regarding transaction management will also be greatly appreciated!


texts: Postgresql 9.2.1, vacuum freeze process stuck while running

texts:
In postgresql 9.2.1, database avoid accepting commands to prevent wraparound dataloss issue.I was running vacuum freeze verbose process in single user mode in the respective database. Suddenly, the vacuuming process got stuck in the half way through (just frozen while vacuuming some transactions) with the warning message,

WARNING: database "XXXX" must be vacuumed within 997218 transactions.
HINT: To avoid a database shutdown, execute a full-database VACUUM in "XXXX".

What would be the reasons for that vacuuming process can stuck in
half way ?
How can i prevent it ?

Please help me.


texts: Understanding two NICs requirement for SQL fail over cluster

texts:
I am trying to make an SQL Server 2012 failover cluster. I have two DB machines. I understand that both machines need 2 NICs each. In my organization the IP scheme assigned is something like 192.168.1.X.

So I want to know if I assign 1 IP to each NIC, will that be enough? Like assigning 192.168.1.50, 192.168.1.51, 192.168.1.52 and 192.168.1.53?

Or two of the NICs in each machine have to have some private network scheme which will allow them to communicate directly?


texts:
I have inherited a (quite) big database (for my standards at least, since by no means I call myself a DBA or DB dev).
So, on this DB they used a unique natural string as the primary key on EVERY table. In my novice experience it seems that is slowing joins down.
Just to get some more info on this, our main table is having roughly 1.5 million rows, the key is a taxing office id number, which might have some leading 0s, so they went and declared this as a varchar(12), in order not to loose the leading 0.
One of our secondary table has more than 3 million rows, and an other one with roughly the same number of rows as the main table. Each of these tables (and many more of smaller size) are using the same varchar(12) as a foreign key to the main table mentioned before.

Questions

Would it make any difference using an int instead of this varchar as
key?
If 1=Y then : There are a few applications that are using this
database and they are all using this key for joining tables. Is
there a way to keep the old columns for joining the tables, but get
some advantage using some int column for the key? i.e. can i change the key, but keep the same old queries?

Edit:
One major limitation on changing the queries is that most are not stored procedures. They are hard coded inside the actual applications


texts: need to use PLSQL to insert distinct rows

texts:
I need to take some data from Table A, use some logic, and then insert one or more rows into Table B.  I have a PLSQL block that brings in data from Table A with a cursor, performs all the logic required, and inserts the required rows.

The problem is there are duplicate rows in Table B - it's the nature of the beast.  But I need the final result to be a Table B with no duplicates.

It's Oracle so temporary tables are bad form - what's the best way to accomplish this?


texts: Postgres 10.3 could not bind IPv6 address, database system was not properly shut down

texts:
I am running Postgres v10.3 through Docker (docker-compose, specifically).  It is not turning back on.  I get the following error message when I try to turn it on:

postgres           | pg_ctl: another server might be running; trying to start server anyway
postgres           | waiting for server to start....2018-05-15 12:37:07.552 UTC [22] LOG:  listening on IPv4 address "127.0.0.1", port 5432
postgres           | 2018-05-15 12:37:07.552 UTC [22] LOG:  could not bind IPv6 address "::1": Address not available
postgres           | 2018-05-15 12:37:07.552 UTC [22] HINT:  Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
postgres           | 2018-05-15 12:37:07.558 UTC [22] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
postgres           | 2018-05-15 12:37:07.804 UTC [23] LOG:  database system was interrupted while in recovery at 2018-05-15 12:35:58 UTC
postgres           | 2018-05-15 12:37:07.804 UTC [23] HINT:  This probably means that some data is corrupted and you will have to use the last backup for recovery.
postgres           | .........2018-05-15 12:37:17.735 UTC [23] LOG:  database system was not properly shut down; automatic recovery in progress
postgres           | .2018-05-15 12:37:17.935 UTC [23] LOG:  redo starts at 1/5E964BA8
postgres           | pg_ctl: server did not start in time
postgres           | ................................................. stopped waiting

I may have shut down the database as I was writing to it, which is the only thing I can think of that would have caused this.  I do not have a backup which is why I'd like to restore what I've already done (except for the table that I was writing to when I shut down).

Things I've tried:

Restarting the docker container and clearing out the cache (docker system prune -f)
Remove the postmaster.pid file and restarting

This gives me a could not open file "postmaster.pid": No such file or directory error and generates a postmaster.pid file which then allows me to get back to my original error


texts: Does the || operator in Postgres result in a blank result if one of the columns is blank?

texts:
I am running a query in Postgres using the || operator to concatenate some columns and it seems where one of the columns is blank the whole result is blank. Is that normal || behaviour?

With "blank" I mean NULL.


texts: How to check connection string in SSMS2012?

texts: I'm connected to database. I use db by Management Studio 2012 Express. Can I check connection string by click something in Management Studio?


texts: Decrypting a Master Key works well using SQLCMD but not in the script

texts:
I would like to use a script to automatize the process of migration of a Db in another instance. The DB is encrypted, so after restore it I need to decrypt it and encrypt again using the new Service Key.

Quite easy from SQLCMD line after restore (SQLCMD -S.\myinstance )

USE [MyDatabase];
GO
OPEN MASTER KEY DECRYPTION BY PASSWORD = '...';
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
GO

And everything goes flawlessy

If instead I put it in a script (I would like to store the password and the dbname in variables) and I run it I receive the error message

Cannot find the symmetric key 'master key', because it does not exist or you do
not have permission.

Of course I'm running the script within the same user (SQL -S.\myistance -i script.sql)

declare @dbMKey nvarchar(100);
DECLARE @exec_sql nvarchar(max);

    SET @exec_sql = 'OPEN MASTER KEY DECRYPTION BY PASSWORD = N' + quotename(@dbMKey,'''') + '; ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY; CLOSE MASTER KEY;';
    print @exec_sql;
    EXEC sp_executesql @exec_sql

I add a print for debugging purpose to be sure that the sql command was exactly the same  I wrote by hand.

Anyone has some idea?


texts:
What's the best way to add columns to large production tables on SQL Server 2008 R2? According to Microsoft's books online:

  The changes specified in ALTER TABLE are implemented immediately. If the changes require modifications of the rows in the table, ALTER TABLE updates the rows. ALTER TABLE acquires a schema modify lock on the table to make sure that no other connections reference even the metadata for the table during the change, except online index operations that require a very short SCH-M lock at the end.

(http://msdn.microsoft.com/en-us/library/ms190273.aspx)

On a large table with millions of rows, this can take a while. Is taking an outage the only option? What's the best way to handle this kind of situation?


texts: what does I/O affinity automatically do when cpu affinity is manually set

texts: I have a 2016 Enterprise db server with 4 processors and 24 cores which is CAL licensed. So only the first 20 cores are used. Now I would like to set affinity so SQL Server will not use the first 20 cores (6 of the first 3 processors and 2 of the 4th processor), but will use 5 cores per processor. What do I do with I/O affinity? Leave it on 'automatically'? When both CPU affinity and I/O affinity are not set to 'automatically', you have to choose between CPU and I/O. When I manually select 20 of 24 cores for CPU affinity, will the 'automatically' setting for I/O affinity mean that only the remaining 4 cores will be used for I/O?


texts: AlwaysON AG Readable Secondary is set to 'Yes'. How to connect from the client?

texts:
For AlwaysON AG, if Readable Secondary is set to 'Yes', do you use the listener name or the secondary replica instance name to connect for read-only purposes?

I know when Read Secondary is set to 'Read-intent only', you are required to specify ApplicationIntent=readonly and the database to be routed to the secondary replica. However, my understanding is that if you set it to 'Yes' instead of 'Read-intent only', you have to explicitly specify the secondary replica instance name instead of the listener name. Is that correct?


texts: Cannot Deploy SSIS Package in SQL Server Data Tools 2015 (Destination server name doesn't allow password entry)

texts:
I'm extremely frustrated.  I'm using SQL Server Data Tools 2015.  I have an Integration Services project and I want to deploy a single package of this solution to SQL Server 2012.  I've set the solution to Deployment Target Server Version to SQL 2012.  I right click on the package and select Deploy, however at the Select Destination part of the wizard, it only allows me to select a server name (and not enter any credentials).  However, I need to enter a specific username and password as my domain account is not a valid login.

I understand that that wizard will eventually run isdeploymentwizard.exe and I looked at the parameters for it as well and do not see a way to enter any connection information outside of a server name.

How can I deploy this package to the server using a username and password instead of a trusted account?  I feel like I must be missing something incredibly stupid, because I was unable to find someone with a similar issue even after searching Google for an hour.  Apparently everyone deploying to SQL Server from SSDT2015 is using a trusted account.

Edit: In older versions of BIDS, you could open the project and select File > Save Copy of .dtsx as then save it to the SQL server and use credentials.  However in SSDT, the Save Copy of option only allows you to save the package locally.


texts: Can I move / Modify the Object in Object Storage Service (OSS)

texts:
I am using Alibaba Cloud - ECS and have Storage Bucket - Object Storage Service (OSS).

File Storage structure is like

oss:/bucket/abc/xyz/123.jpg/

My Question is

Can I move or modify the objects in OSS?


texts:
I am trying to restore database created on server to my localbox however i am getting 'Error: Specified cast is not valid. (SqlManagerUI)' error message.

Server Info:
Windows Server 2008 Standard 64 bit
Microsoft SQL Server 2008: (Info obtained from SSMS About page)

    Microsoft SQL Server Management Studio      10.0.4064.0
Microsoft Analysis Services Client Tools        10.0.4064.0
Microsoft Data Access Components (MDAC)         6.0.6002.18005
Microsoft MSXML                                 3.0 6.0
Microsoft Internet Explorer                     8.0.6001.19190
Microsoft .NET Framework                        2.0.50727.4216
Operating System                                6.0.6002

My Localbox:
Windows 7 64bit
Microsoft SQL Server 2008:

Microsoft SQL Server Management Studio          10.0.4064.0
Microsoft Analysis Services Client Tools        10.0.4064.0
Microsoft Data Access Components (MDAC)         6.1.7601.17514
Microsoft MSXML                                 3.0 4.0 5.0 6.0
Microsoft Internet Explorer                     9.0.8112.16421
Microsoft .NET Framework                        2.0.50727.5448
Operating System                                6.1.7601

Does anyone know how to fix this issue?

Sanjeev


texts:
I'm trying to lauch a system call from Oracle Apex, directly or indirectly. It seems to only accept PL/SQL procedures, so I iterated towards this:

begin

  :p3_request_id := sequence.nextval;

  insert into request (id, user, target, operator)
  values (:p3_request_id, :p3_user_id, :p3_target_id, :app_user);

  -- part that doesn't work:
  host('/home/oracle/scripts/dbq/fork.py ' || :p3_request_id);

end;

Unfortunately:

ORA-06550: line 9, column 3: PLS-00201: identifier 'HOST' must be declared
ORA-06550: line 9, column 3: PL/SQL: Statement ignored

Upon further research, I've come to discover that for this to work I must declare this procedure and run a background daemon script... This seems rather convoluted and fragile (one more process to worry about, monitor, etc.). Is there any other way to launch a system process?


texts: How do you stop, take offline, and delete a SQL Server database in recovery?

texts:
I have a development database which has entered recovery mode on restart, and has been recovering for the last hour or so.

I need to either stop the recovery, or kill it in some way.

I don't care one bit about the database or any of the data, I have deployment scripts.

Any ideas?


texts:
I installed rh-postgresql12 in my server. These are the steps I used to install and enable it and its dependencies :

Installation :

yum -y install rh-postgresql12 rh-postgresql12-postgresql rh-postgresql12-postgresql-contrib rh-postgresql12-postgresql-devel rh-postgresql12-postgresql-libs

Enabling with scl :

scl enable rh-postgresql12 bash

Initialize and start :

postgresql-setup –initdb

service rh-postgresql10-postgresql start

However, when I do a "find" for psql :

[root@zabbixtest3 ~]# find / -name psql
/usr/bin/psql
/opt/rh/rh-postgresql12/root/usr/bin/psql

There are 2 of it, and the first one is for psql 9.2. I need to use only rh-postgresql12. How do I use it without invoking the entire path :

-bash-4.2$ /opt/rh/rh-postgresql12/root/usr/bin/psql -U postgres postgres
psql (12.1)
Type "help" for help.

postgres=#


texts: SSMA from oracle xe to sql server express PK violation

texts:
I'm using SSMA for the first time trying to migrate an Oracle XE schema to SQL Server Express. The majority of the tables get created and their data migrated just fine. There are a handful of tables though that keep giving me primary key violation.

It tells me the duplicate key value, but when I query the Oracle table for that key there is only 1 record (which makes sense since it has the PK on it).

I'm trying to figure out what I could be missing here because this can't be the real error. I mean the oracle table has the PK on it so it can't, and doesn't, have a duplicate value. So why is SSMA giving me seemingly a false error on this when clearly the source oracle table doesn't have this duplicate key in it?


texts:
I have Query Store (QS) running on a SQL 2017 instance.  Currently at RTM, with RTM CU13 currently in test, to be applied on prod in next month's patch window.

While most queries and reports return results quickly with little to no impact, anything thing I try to look at around waits is problematic. CPU usage climbs from 20 to 80 percent and stays there for minutes until I kill it.  This is 24/7 production system so if I really want to look at QS waits I am going to need to do it someplace else.

The database is 150GB with 1000MB of space for QS.  I have a sandbox with 10GB of space so if I could get the QS data out, I could play with it there.

I looked around and I am not finding how to do this.  The best I found was is this sql.sasquatch 2016 post with a 2016 answer by Erin Stellato

  At present, there is no option to export and/or import Query Store data, but there is a Connect item to vote for: https://connect.microsoft.com/SQLServer/feedback/details/2620017/export-query-store-tables-separately-from-the-database-tables

Note: The link goes to a redirect "Microsoft Connect Has Been Retired" Looks like the actual link should be to https://feedback.azure.com/forums/908035-sql-server/suggestions/32901670-export-query-store-tables-separately-from-the-data

Looking at Microsoft, I find that most everything you might use to access the data is a View, Stored Procedure or Report. I am not seeing a way to just extract all the QS stuff out of database.

Examples of direct queries, use the views Example by Kendra Little I have toyed with the idea of just doing a Select * from the views and exporting the results to my sandbox.  But as I have not found anyone talking about it, I am not sure it is good idea.

Related

Best Practice with the Query Store
How Query Store Collects Data
Query Store Catalog Views
Query Store Stored Procedures
Monitoring performance by using the Query Store

Additionally  I would like to be able to keep the Query Store results from pre-CU13 to use as a baseline to compare post CU13.

Edit after first answer and edits to same
Recent edits to the answer by jadarnel27 adds good info, but I don't care about the user interface, I want to be able to query the data without altering the database or impacting performance. As a secondary goal I would like to be able to archive the QS data, so I can review previous performance (i.e. before and upgrade, but after the old QS data would have purged off)


texts: subquery must return onl one column in postgresql

texts:
I want to get the XML response after the stored procedure call in PostgreSQL. In the below query I was trying to get the output, but it throws an Error: subquery must return only one column. Can someone help me with this?
SELECT (
        SELECT xmlforest('2021_PPP_Parasiticide_Program' AS Name, CASE
                    WHEN "temptable"."C_2021_PPP_PARA" IS NOT NULL
                        THEN 'True'
                    ELSE 'False'
                    END AS requirementsMe, coalesce("temptable"."YTD_2021_Qualifying_Carton_Purchased", 0) AS qualifyingPurchaseAmount, 'Dollar' AS qualifyingPurchaseAmountType, CASE
                    WHEN "temptable"."C_2021_PPP_PARA" IS NOT NULL
                        THEN 'Active'
                    ELSE 'In-Active'
                    END AS componentStatus)
            ,(
                SELECT (
                        SELECT xmlforest('q1_parasiticide_carton_rebate' AS rewardName, CASE
                                    WHEN q1_parasiticide_carton_rebate IS NULL
                                        THEN CAST(0 AS VARCHAR)
                                    ELSE cast(coalesce(q1_parasiticide_carton_rebate, 0) AS VARCHAR(10))
                                    END AS rewardAmount)
                        FROM temptable
                        )
                    ,(
                        SELECT xmlforest('q2_parasiticide_carton_rebate' AS rewardName, CASE
                                    WHEN q2_parasiticide_carton_rebate IS NULL
                                        THEN CAST(0 AS VARCHAR)
                                    ELSE cast(coalesce(q2_parasiticide_carton_rebate, 0) AS VARCHAR(10))
                                    END AS rewardAmount)
                        FROM temptable
                        WHERE temptable.rus_id = temptable.rus_id
                        )
                FROM temptable
                )
        FROM temptable
        );

I want response in the below format -
<CustomerProgramStatus_response>
   <programComponents>
      <name>2020_PPP_Parasiticide_Program</name>
      <requirementsMet>FALSE</requirementsMet>
      <qualifyingPurchaseAmount>0</qualifyingPurchaseAmount>
      <qualifyingPurchaseAmountType>Dollar</qualifyingPurchaseAmountType>
      <componentStatus>In-Active</componentStatus>
      <componentRewards>
         <rewardName>Q1_Parasiticide_Carton_Rebate</rewardName>
         <rewardAmount>0</rewardAmount>
      </componentRewards>
      <componentRewards>
         <rewardName>Q2_Parasiticide_Carton_Rebate</rewardName>
         <rewardAmount>0</rewardAmount>
      </componentRewards>
   </programComponents>
</CustomerProgramStatus_response>


texts: Influxdb and grafana: howto create series that is the sum of two other series

texts:
I have these three queries. They show the average upload speed of three different docker containers over time:

The difference() call is needed, because the data contains cumulative "bytes_sent" values, and it never decreases.

Here is an example of the output:

I would like to create a third series that shows the sum of the above three graphs. If I simply remove the WHERE condition the it won't work:

The reason for this is that mean() always precedes difference(), and the mean value of these independent cumulative sums is meaningless:

The correct solution would be to calculate the three difference values and add them together. But I don't know how to do this in grafana?

Obviously the very best solution would be to store the differences in the database in the first place. Unfortunately, I'm not the one who is sending the data so I can't do anything about it.


texts: sql syntax error 1064 in workbench-generated script

texts:
I'm not very familiar with sql, so I'm using workbench to create a db. I keep getting this error:

Executing SQL script in server
ERROR: Error 1064: You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax to use near ')
    REFERENCES `project2`.`REF` ()
    ON DELETE CASCADE
    ON UPDATE CASCADE' at line 6
SQL Code:
        CREATE TABLE IF NOT EXISTS `project2`.`REF_AUTH` (
          `REF#` INT(11) NOT NULL AUTO_INCREMENT,
          `RAUTHOR` VARCHAR(45) NOT NULL,
          PRIMARY KEY (`REF#`, `RAUTHOR`),
          CONSTRAINT `REF#`
            FOREIGN KEY ()
            REFERENCES `project2`.`REF` ()
            ON DELETE CASCADE
            ON UPDATE CASCADE)
        ENGINE = InnoDB
        DEFAULT CHARACTER SET = big5

SQL script execution finished: statements: 4 succeeded, 1 failed

Any help would be greatly appreciated.


texts: How do I determine the current "idle timeout" value for PostgreSQL?

texts:
Sigh. I've been searching forever for this and, as usual, find nothing.

I'm not talking about the "connection timeout" for how long it waits before giving up when trying to connect to the database. I'm talking about how long a database connection can be "idle" without any queries being executed (and no pg_pings) before it's considered abandoned and force-closed.

Which setting controls this, and how do I retrieve it?


texts: Why would one use the Innodb Plugin instead of the builtin Innodb in Mysql 5.1? (GPL)

texts:
Innodb works in Mysql 5.1 whether or not the innodb plugin, ha_innodb_plugin.so is loaded at startup or not. Configuration dependent of course.

What are the advantages and disadvantages of either using or not using the builtin or modular innodb features?

Thanks very much for any explanation :)


texts: How to implement database schema for tracking the status of a purchase?

texts:
It's an internet stop, physical products with delivery.

I'm trying to figure out how to implement transactions/payments, fulfilment, shipping, payments, refunds, etc.

Namely, a status of each sale.

Approach #1:

create a column "status" in "sales". It could the following values:

new,
payment_pending,
paid,
fulfilment_started,
fulfilled,
shipping_started,
shipping_in_progress,
delivered_successfully

It may also contain:

refund_claimed,
refund_in_progress,
refunded.

This isn't the complete list of statuses

Approach #2:

create tables for each process:

payment_logs (id, sales_id, status, details)
shipping_logs (id, sales_id, name, status, details)
refund_logs (id, sales_id, status, details)

The approach #2 is more flexible. Yet, there's a downside: how would I know the current global status of a sale?
  Is it being delivered?

Or Is it still being fulfilled?

Or is a payment pending for it still? That is, it's not even proceeded to fulfilment, nor delivery procedures.

To solve that, I'd have to introduce logic to this approach: search in "payment_logs".
Found, successful? ->  proceed to "fulfilment_logs". Found, but not yet "successfully_fulfilled"? Then the global status of a "sale" will be whatever the current status in "fulfilment_logs" is.

Will this, the approach #2, work nicely? Are there any other downsides?

What would you propose?


texts: How to join two tables with different numbers of rows

texts:
I've already search the net about this but it can't solve my problem.

I have two tables one with 10 rows and other with 9 rows

table 1

RNUM       JOB_ID
---------- ----------
1          AC_ACCOUNT
2          AC_MGR
3          AD_ASST
4          AD_PRES
5          AD_VP
6          FI_ACCOUNT
7          FI_MGR
8          HR_REP
9          IT_PROG
10         MK_MAN

table 2

RNUM       JOB_ID
---------- ----------
1          AC_ACCOUNT
2          AC_MGR
3          AD_ASST
4          AD_PRES
5          AD_VP
6          FI_ACCOUNT
7          FI_MGR
8          HR_REP
9          IT_PROG

I want to join these two tables but I want the output to be like this

RNUM       JOB_ID            RNUM       JOB_ID
---------- ----------        ---------- ----------
1          AC_ACCOUNT        null       null
2          AC_MGR            1          AC_ACCOUNT
3          AD_ASST           2          AC_MGR
4          AD_PRES           3          AD_ASST
5          AD_VP             4          AD_PRES
6          FI_ACCOUNT        5          AD_VP
7          FI_MGR            6          FI_ACCOUNT
8          HR_REP            7          FI_MGR
9          IT_PROG           8          HR_REP
10         MK_MAN            9          IT_PROG

my first code is

select a.*, b.*
from samp a, samp2 b where a.rnum = b.rnum(+)

but the result of my code is

RNUM       JOB_ID                RNUM       JOB_ID
---------- ----------            ---------- ----------
1          AC_ACCOUNT            1          AC_ACCOUNT
2          AC_MGR                2          AC_MGR
3          AD_ASST               3          AD_ASST
4          AD_PRES               4          AD_PRES
5          AD_VP                 5          AD_VP
6          FI_ACCOUNT            6          FI_ACCOUNT
7          FI_MGR                7          FI_MGR
8          HR_REP                8          HR_REP
9          IT_PROG               9          IT_PROG
10         MK_MAN                null       null

I want to connect table 2's number 1 entry(AC_ACCOUNT) to table 1's number 2 entry(AC_MGR) as what you can see in my desired output.

I used rownum as a technique just to have a connection between my two tables.

I think I misunderstood left outer join here, please kindly enlighten me how to achieve my desired output.


texts: select random tuples but prefer old dates

texts:
With this table:

CREATE TABLE T_TEST (
id int identity,
somevalue varchar(256),
lastupdate smalldatetime
);

i want to select n random tuples which age is older than x days but prefer old tuples (where the lastupdate column is smallest)

my current query, x=30, n=1000:

select top 1000 * from T_TEST where lastupdate < dateadd(day, -30, getdate()) order by newid()

It should not work like this:

select * from (select top 1000 * from T_TEST where lastupdate < dateadd(day, -30, getdate()) order by lastupdate desc) t order by newid()


texts: sqlserver lsalert job does not update it's out-of-sync counter

texts:
I am getting 14421 error messages and ended up reading the following KB

Here is the part I'm referring to :

  Problems either with the Backup job or Copy job are most likely to
  result in "out of sync" alert messages. If "out of sync" alert
  messages are raised and if there are no problems with the Backup or
  the Restore job, check the Copy job for potential problems.
  Additionally, network connectivity may cause the Copy job to fail.

Even though no monitor server is in use I think this is what's happening :

my primary database writes logs to it's server's disk (LSBACKUP)
standby database retrieves to log to a local destination (LSCOPY)
standby database applies the log (LSRESTORE)
Once LSRESTORE is done applying the log it supposedly updates a table which the exec sys.sp_check_log_shipping_monitor_alert procedure relies on. This update is sent from the standby to the production and fails to connect somehow.

This server was migrated before and probably has configuration issues as there are only 25 out of 93 databases on this server using log shipping. Yet, all 93 come out from the return of this procedure telling me they are 175 000 minutes late.

On the standby the view is working normally, should I expect the "last restored date" to be updated on the primary server too or is this not a thing ?


texts:
I moved data file and database started fine but I did the following steps:

SQL> shutdown
SQL> startup nomount
SQL> alter system set control_files='/var/oracle/oradata/control.dbf' scope=spfile;
# shut immediate
# chown oracle:dba /var/oracle/oradata/control.dbf
# chmod 755 /var/oracle/oradata/control.dbf
# su – oracle
sqlplus / as sysdba
SQL> startup

Total System Global Area 1068937216 bytes
Fixed Size                  2233344 bytes
Variable Size             813698048 bytes
Database Buffers          247463936 bytes
Redo Buffers                5541888 bytes
Database mounted.

ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/var/oracle/oradata/system.dbf'
ORA-01207: file is more recent than control file - old control file

and now I'm unable to resolve this error.


texts: dbms_datapump API exclude statistics during import

texts:
We are using dbms_datapump to copy data between different servers or to setup new schemas quickly.

However importing statistics usually takes ages and has the additional disadvantage that schema statistics are locked after the import.

So I'm trying to find a way to skip importing the statistics.

According to Metalink Doc ID 1392981.1 this should be possible using:

dbms_datapump.metadata_filter(handle => l_job_handle,
      name => 'EXCLUDE_PATH_LIST',
      value => '''STATISTICS''');

However when I try that, I get an "ORA-39001: invalid argument value" error.

I also tried various other formats found in different places:

dbms_datapump.metadata_filter(handle => l_job_handle,
      name => 'EXCLUDE_PATH_LIST',
      value => 'STATISTICS');

dbms_datapump.metadata_filter(handle => l_job_handle,
       name => 'EXCLUDE_PATH_EXPR',
       value => 'like ''%/TABLE/STATISTICS/TABLE_STATISTICS''');

dbms_datapump.metadata_filter(handle => l_job_handle,
       name => 'EXCLUDE_PATH_EXPR',
       value => 'like ''%STATISTICS%''');

But all of them return with an ORA-39001.

The version I'm using is:

  Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

Operating system is a Windows Server, but this also occurs on a Linux installation.

Any ideas how I can skip the statistics during import (or export) through the DBMS_DATAPUMP API?


texts:
I have a Windows Server 2012 Failover Clustering Instance, hosting a SQL Server 2012 Enterprise Cluster, via NODE1 and NODE2.

I need to add a new read-only Replica to this cluster: NODE3, which is a Windows Server 2012 R2.

I've activated SQL Server AlwaysOn to both FCI NODE1 and Node2.

I've tried to add the new node, NODE3, to FCI cluster services, however I'm seeing this error

  cluster node could not join the cluster because it failed to
  communicate over the network

What do I need do to add NODE3 as a read-only replica in FCI?


texts: Oracle delete technique for table's segment size (data compactness)

texts:
I would like to ask if there is any effect on the following deletion technique of a database to the tablespace size. The existing code for clearing the data has the following style of code before:

TRUNCATE TABLE WORK_T_TABLE;

INSERT /*APPEND*/ INTO WORK_T_TABLE
SELECT * FROM T_TABLE WHERE UPDATE_DATE > SYSDATE - 14;

TRUNCATE TABLE T_TABLE;

INSERT /*APPEND*/ INTO T_TABLE
SELECT * FROM WORK_T_TABLE;

I changed the table into a simple delete statement since I thought this was doing unnecessary truncate and insert.

DELETE FROM T_TABLE WHERE UPDATE_DATE < SYSDATE - 14;

Does the code deletion has any significant meaning in oracle? Does this reduce the unnecessary increase in the table's segment size? Currently, since the sql code change, the table's segment size is increasing rapidly without change to the data record count.

Note that the T_TABLE is always being deleted and inserted data every day. It runs the above delete command to keep 14 days of data only. Then the new data is inserted for the current day. The table's segment size increased by 400% within a month but the record count just increased to 150%.

What I mean by table's segement size is from the value of the code below by the following value:

SELECT BYTES FROM USER_SEGMENT WHERE SEGMENT_NAME = 'T_TABLE';

Also, data is being inserted via the insert select. The values are first loaded via sqlloader to the WORK_T_TABLE then appended to the table via some condition.

INSERT /*APPEND*/ INTO T_TABLE
SELECT * FROM WORK_T_TABLE;

Note: i changed the wording of question from tablesize to table's segment size to address the Justin's clarification.


texts: SQL Server Index recomended but already there (DMVs)

texts:
I am using the DMV views to get a list of index recommendations and then I am reviewing each of them to make sure they make sense and don't overlap etc.

Some examples are

Index ON X ( Y ) and Table X already has the INDEX Y.  So far this is for non clustered indexes and the indexes have been defragmented.

SQL Server 2008 R2, database is fairly busy and lots of index recommendations are coming up.  Right now it's hard to see the wood for the trees; I am just trying to get some low hanging fruit to start with.

I have checked the last access times and they're all recent.

SQL Server version I'm using is:

  Microsoft SQL Server 2008 R2 (SP2) - 10.50.4033.0 (X64) Jul 9 2014 16:04:25 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.2  (Build 9200: )


texts: Need help designing database, Create a table with additional columns/fields in the table, or create a new table for each different type?

texts:
So the tables that I have are Certificates, Approvers, CertificatesApprovers, Categories, Approvees, Status, AdditionalFields, AdditionalFieldsValues.
Many Certificates to Many Approvers relationship.
1 Category to Many Certificates relationship.
1 Category to Many AdditionalFields relationship
1 AdditionalFields to Many AdditionalFieldsValues relationship
If there are different types of Certificates which will have different fields/columns, should I create a different unique table for each type of Certificate or just add the additional fields/columns in the Certificates table (but some of the entries will be blank)?
For example, a MedicalCertificate will have a field Specialization whereas a DrillCertificate will have a field DrillType.
Certificates Table

ID
CategoryID
ApproveeID
ValidFrom
ValidUntil
StatusID
Specialization
DrillType

1
2
4
1/4/2021
2/4/2021
1
Oncology

2
1
5
1/4/2021
2/4/2021
1

Traditional

OR
MedicalCertificates Table

ID
ApproveeID
ValidFrom
ValidUntil
StatusID
Specialization

1
4
1/4/2021
2/4/2021
1
Oncology

2
5
1/4/2021
2/4/2021
1
Haemotology


texts: AWS RDS: what to do when a replica is promoted

texts:
I have two RDS servers, one main and the other a replica of the main one. Let's call them server #1 and server #2.

Sometimes the roles switch between the servers so server #2 is the main DB (write role) and #1 is the replica (read role). Probably because of an upgrade or some other event, don't see anything in the logs.

The switch is problematic because we have two kinds of apps: read/write apps that connect to the writer cluster endpoint and read-only apps that connect to the replica. The switch of the DNS for the writer instance is handled by AWS through a CNAME switch, but the read endpoint never gets a DNS switch so after the change of roles all my read-only apps connect to the new main DB.

Is there a way to handle this automatically? I could create a manual CNAME record and switch it when I see this has happened, but this is neither reliable (I'm not notified when the replica gets promoted) neither automatic.


texts: Execution plan results for sp_executesql suggests adding missing index for already existing index

texts:
I'm analyzing a query being run inside our system via sp_executesql. I am able to execute the query on the production database and include the actual execution plan. In the execution plan results window, a simple nonclustered index is suggested.

Based upon the suggestion, I have added the index. However, when I re-run the same query, it continues to suggest the same index. Why would it think that the index does not exist?


texts: Are passwordless logins like ##MS_SQLResourceSigningCertificate## , ##MS_AgentSigningCertificate## a vulnerability?

texts:
I'm new to MS SQL Server.

Colleagues working in IT security, have run an scan showing come DB users with null password.

Some of them are ( # included )

##MS_SQLResourceSigningCertificate##
##MS_SQLReplicationSigningCertificate##
##MS_SQLAuthenticatorCertificate##
##MS_PolicySigningCertificate##
##MS_SmoExtendedSigningCertificate##
##MS_AgentSigningCertificate##

I suspect they are not a security threat but, since they don't use Windows Authentication either, I don't know how to support that assumption.

What are these logins for ?


texts: MYSQL inner join , if not match try another table

texts:
I've got a very quick issue here, how can i join a table with two other tables in a way if one's info doesnt match, it goes try another one. something like:

  $properties = $mysqli->query("SELECT *
                                FROM invoices i
                                inner join properties p on i.ref_id = p.property_id
                                if_null_then (
                                  inner join properties2 p2 on i.ref_id = p2.property_id)
                                ") or die (mysqli_error());

Much appreciate any response guys, i'm kinda stuck now :/


texts: fix oracle 11.2 asm performances (bad disks repartition)

texts:
i've a single Oracle 11.2 node with really bad performances and i figure out that there's a lun always running at 100% IO.

This is the "DATA_0000" lun configured in asm in this way

SQL> SELECT name, header_status, path FROM V$ASM_DISK;

NAME                           HEADER_STATU

PATH

DATA_0000                      MEMBER
/dev/dm-6

INDICI_0000                    MEMBER
/dev/dm-7

I'm not a dba at all, but i think this is  a really bad configuration causing bad performances. I'm planning to add 2 more luns (same size and storage enclosure as the originals) but i would like to know if is it possibile (without loosing data) to split existing luns between both VG

AS IS

dm-6 => DATA_0000 => DATA   500gb

dm-7 => INDICI_0000 => INDICI   500gb

To BE

dm-6    => DATA_0000
        => INDICI_0001

dm-7    => DATA_0001
        => INDICI_0000

Any help would be welcome.
Regards


texts: Actual Query Plan Estimated Cost and dm_exec_query_stats worker time do not gel

texts:
I cleared my stats and ran my query. The actual execution plan has a total estimated cost of 0.61. I used the total_worker_time column from the dm_exec_query_stats dmv to calculate an average of 20858 microseconds of CPU time:

(SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count))

The plan recommended an index and I created that index. I cleared my stats and I ran the query again. This time the plan has a total estimated cost of 0.37 at the top level. I checked the dm_exec_query_state dmv again and now the average CPU time is 51536 microseconds.

I was expecting the worker time to be around half, not over double! Am I missing something here?  Why is the improvement to the query plan not reflected in the exec query stats?  The two plans are uploaded here:

Plan1

Plan2


texts: How do I map SSIS 2012 Package Variables to Parameters?

texts:
I have created multiple packages that use variables to assign Connection Manager Expressions such as Connection String. However in the new SQL 2012 Project Deployment Model I don't seem to be able to configure these variables.

Would I therefore need to create these Parameters for the Environment and map them to specific Package Variables? Is this even possible and how is this done?

This link describes configuring the Parameters, but not associating to package Variables.

Further to this, it seems I could set a Varaiable Expression to be a Project or Package Parameter, however this seems a bit convoluted. Perhaps I should stop using Variables altogether?

Update: I've now cut-over to using Project-level and Package-level Parameters (instead of Package Variables) when I need to modify per-environment settings such as file paths and connection strings. Perhaps this was the intent from MS: any best practice advice is of course, still welcome.


texts: SQL server Profiler can be used any time?

texts: I want to run SQL Profiler for Long running queries in SQL server 2016. what is the best practice, without effecting the SQL performance in less time and more productive?


texts:
I downloaded and installed phpMyAdmin 4.8.5 on my windows. Upon opening it in the browser, it immediately asks for a Username and Password. I tried to submit root for username while leaving the password black. I even try password as password. Unluckily, both didn't work by default.

So I checked the web and I saw this from stackoverlow. Unfortunately, all of the given answers did not work for me and hello no, the thread is already closed.


texts: Disk Read IO Throttle not showing in perfmon Windows Server 2012 r2

texts:
I am working on Windows Server 2012r2,SQL Server 2014.

I am trying to create a new data collector to review Disk IO in perfmon. When I get to the step to add the available counters there is no Disk Read IO Throttle per sec in the list to be added.

I did see it in screenshots in other web resources. Is there a way to add it in the perfmon so that it appears as a counter?


texts: Read Committed Snapshop Isolation Level - Effect of NOLOCK

texts: As I understand it, with Read Committed Snapshot, readers don't block writers and writers don't block readers. The only blocking that can occur is writers blocking writers. By default our database is set to Read Committed Snapshot. However, we have a lot of users issuing queries using WITH (NOLOCK) (out of bad habits). If I understand correctly, a query that uses WITH (NOLOCK) changes the isolation level. When this is used in a query, is it now possible for a reader to block a process attempting to update the data being read?


texts: Initializing values to a custom data type

texts:
I have defined a custom data type which I need to use in my procedure. Give below is my data type.

CREATE or REPLACE TYPE CUSTOM_DATATYPE AS object(
chkinvpre    VARCHAR2(100),
orderid      NUMBER(10),
deldate      DATE,
fowno        VARCHAR2(40),
fowname      VARCHAR2(40),
fowadd1      VARCHAR2(40),
fowadd2      VARCHAR2(40),
fowadd3      VARCHAR2(40),
fowadd4      VARCHAR2(40),
packtype     VARCHAR2(40),
noofpacktype VARCHAR2(40),
gweight      VARCHAR2(40),
dimension    VARCHAR2(40),
hawb         VARCHAR2(40)
)
NOT final;

Now, I am trying to initialize this data type to call my  procedure . Given below is the code.

declare
rajesh CUSTOM_DATATYPE;
begin
 rajesh:=('xyz',123,'13-02-14','12','fdfd','add1','add2','add3','add4','packtype','noofpacktype','1234','dfs','dde');
  DN_Generated(rajesh);
end;

/

And here is the error that I am getting.

Error starting at line 1 in command:
declare
rajesh CUSTOM_DATATYPE;
begin
 rajesh:=('xyz',123,'13-02-14','12','fdfd','add1','add2','add3','add4','packtype','noofpacktype','1234','dfs','dde');
  DN_Generated(rajesh);
end;
Error report:
ORA-06550: line 4, column 10:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 4, column 2:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

Please let me know the error.
Thanks in advance.


texts: Can someone explain the magic of Opendatasource/Openrowset?

texts:
When pulling information from an excel file (or ms-access DB) I commonly use something like this:

SELECT *
 FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
 'Data Source=C:\test.xls;Extended Properties=''EXCEL 12.0;HDR=NO;IMEX=1'' ')...[Sheet1$]

Sometimes it works. Sometimes it doesn't.

Does anyone know a guide to setting this up?

I know about the temp folder, I know about downloading the correct drivers, I know about the extended properties, I know about not having the file open. Sometimes though, I still get the -

  OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error".

I ran into this problem yesterday. I restarted my machine - didn't work. Then restarted my instance again and P00f! Magically it worked.

So that's my question - When getting the awesome "Unspecified error", what do you need to check to make sure all the stars are in line for this to work.


texts: Log Shipping- Can I alter the schema of a table that is being used in the secondary instance?

texts:
We have two SQL Server instances with log shipping set on a database.
Can I alter the schema (add columns, remove columns etc.) of the table in the Primary instance/db while the table is being used in the Secondary instance/db?


texts: Why is my dev server faster than a much more powerful RDS?

texts:
I'm running a huge data migration. Probably ~100 million rows total across a few dozen tables.

There is a lot of reformatting. About 50% of it is done through PHP while the rest uses INSERT INTO... SELECT FROM statements with multiple joins.

When I run the script on my dev server (MySQL server is localhost), it takes about 2 hours and 14 minutes total. This machine has 8 GB of RAM, 200 GB storage, and a 4-core 3.1 GHz CPU.

I set up an EC2 instance and an RDS in the same zone (us-east-1a). The RDS is a db.r3.2xlarge instance (vCPU - 8 (cores?), ECU 26, 61 GB RAM, 100 GB storage). I SSH into the EC2 instance and run the script. The PHP portion took 70-80% longer. I expected that, because of network latency connecting to an RDS vs. localhost.

What's more disappointing is that the pure MySQL INSERT queries also took longer. Some of them were the same speed, some were a little faster, but overall they were 35% slower.

The IOPS seemed really low, and the InnoDB buffer usage as well. It really seemed like it wasn't using the resources it has.

Any suggestions? Is this something that Provisioned IOPS could help with?

Update

Different innodb_* variables:

Variable         | Dev server   | RDS
-----------------|--------------|------------
buffer_pool_size | 128 M        | 44 G
flush_method     | (blank)      | O_DIRECT
log_file_size    | 48 M         | 128 M
version          | 5.6.33       | 5.6.19


texts: PostgreSQL, UPDATE CASE statement uses huge amount of space

texts:
I am running a CASE statement on one of my tables and it consumes a huge amount of hard drive space that is never freed up even when i run VACUUM ANALYZE. Autovacuum is ON as well.

I am using PostgreSQL 9.3 64Bit on a windows 2012 server. I have tweaked the postgresql.conf using advice on the wiki. So it is as optimised as I can make it.

The table is large (>150million rows) which I need to add an extra column and populate it based on the contents of 3 other columns. The size of this table is 53Gb without indexes.

Having tested lots of approaches I am using a CASE statement. Two of the columns are arrays so I have used GIN indexes and a standard index.

A sample of my CASE statement looks like this, the rules go up to 38

UPDATE osmm.topographicarea
    SET fcode = (CASE
        --building CASE statements
        WHEN (descriptivegroup @> '{Building}' and descriptiveterm @> '{Archway}') then 1
        WHEN (descriptivegroup @> '{Building}') then 2

        WHEN (descriptiveterm @> '{Step}') then 3
        WHEN (descriptivegroup @> '{Glasshouse}') then 4
        WHEN (descriptivegroup @> '{Historic Interest}') then 5
        WHEN (descriptivegroup @> '{Inland Water}') then 6
      ELSE 99
   END);
COMMIT;

The process takes over 5hrs but adds a huge 180Gb to the table!!

Adding an integer column to this table surely should not do this?

How can I fix this please?


texts: pgAgent not working on linux (Ubuntu 14.04)

texts:
I am running Postgres 9.3 on Ubuntu 14.04 and I am unable to get pgAgent daemon/service to run.

I have confirmed that the daemon is not running using service --status-all.

When trying to run pgagent (whilst logged in as the only user in ubuntu) using pgagent hostaddr=127.0.0.1 dbname=postgres user=user1 the following messages are displayed:

WARNING: Couldn't create the primary connection (attempt 10): fe_sendauth: no password supplied

ERROR: Stopping pgAgent: Couldn't establish the primary connection with the database server.

The lack of running pgAgent was also confirmed when trying to run a job on demand.

The user 'user1' is able to log into pgAgmin and connect to the database from other computers on the local network.

The user looks like this in the pg_hba.conf file:

# Database administrative login by Unix domain socket
local   all             user1                                md5

A .pgpass password file also exists for this user in the home directory.

What is causing the above messages and what needs to be done to get pgAgent to run scheduled tasks?


texts: how to prevent replication to stop if there is no activity for some time?

texts:
On one of my transaction replication publications, when there is no any activity, after some time, the replication stops.

Where can I see these replication settings?

what to put in place to prevent this to happen?


texts: How to exclude non-distinct rows in a query

texts:
I have a table that contains references to clients impacted in a problem. The parent table contains the problem info, specifically the ProblemID (PbMID). Since one problem can affect multiple clients, we store the client impacted data in a child table. The child table contains an ID field for housekeeping, a PbMID field which foreign keys back to the parent table, and a Company field containing the text name of the client.
I have a requirement to pull all the problems were a SINGLE client was impacted. If I use DISTINCT, I get all single client rows, but I also get the FIRST row of a multi-client problem, which is not what I'm being asked for.
Here's the client impacted table example
ID  | PbMID | Company    |
1   | 1     | Company 1  | Valid
2   | 4     | Company 2  | Valid
3   | 6     | Company 3  | Valid
4   | 22    | Company 1  | Invalid
5   | 22    | Company 4  | Invalid
6   | 23    | Company 5  | Valid
7   | 24    | Company 6  | Valid
8   | 25    | Company 1  | Invalid
9   | 25    | Company 8  | Invalid
10  | 25    | Company 10 | Invalid
11  | 26    | Company 2  | Valid
12  | 27    | Company 4  | Valid

The rows marked INVALID would not be included, since they reflect multi-client problems.
So, ideally, the return would be:
ID  | PbMID | Company    |
1   | 1     | Company 1  | Valid
2   | 4     | Company 2  | Valid
3   | 6     | Company 3  | Valid
6   | 23    | Company 5  | Valid
7   | 24    | Company 6  | Valid
11  | 26    | Company 2  | Valid
12  | 27    | Company 4  | Valid

Any help would be greatly appreciated. SQL isn't my forte, so I've been trying to wrap my head around this with no luck.


texts:
Is there a difference performance wise between these 2 examples:
WITH CTE1
    AS (
      SELECT
        Col1,
        Col2,
        Col3
      FROM dbo.Table1
      WHERE Col3 = '1'
    )

select * from CTE1

WITH CTE1
    AS (
      SELECT
        Col1,
        Col2,
        Col3
      FROM dbo.Table1
    )

select * from CTE1
  WHERE Col3 = '1'

We're using a CTE as a subselect within some dynamic sql and can't apply the where clause until the 'select from CTE' happens.  Our actual query is much more complicated than this.  But, I'm wondering if it will be more performant to not use a CTE.  Will the second example actually pull all of the rows from the table and then apply filtering?  Or are these functionally equivalent?  We use SQL Server.


texts: Does SQL Server Clustering Enhance Performance?

texts:
I'm not experienced with SQL Server, I'm a novice.

I'm a developer, and i think the question is more related to a Database Administrator.

I'm working on an old legacy system where over time the Database underneath this system has grown in schema and data over a number of years.

Some tables have around 600,000 records and there are over a hundred tables with a large number of relationships between them.

The systems performance is being severely affected,  as the number of users grows and more tables are added, the queries become larger.

The SQL Server is reporting timeouts daily and the number of timeouts is growing all of the time.

My colleagues have optimized the query plans and indexes in the SQL Server Instance to the best of their ability.

The system runs off one SQL Server Instance, running on a separate server to the where the application is hosted.

Is there any benefit from using a SQL Server Cluster to enhance performance.

I know that its beneficial in terms of maintaining availability if one Instance goes down the other takes over..

But would the workload be shared between SQL Server Instances, i.e queries / writes...

Most importantly would it reduce timeouts?

If there are better , more optimal methods to combat these timeouts, and advice would be great.


texts: Oracle Package in Data Dictionary that can't be dropped

texts:
I've encountered a very interesting problem that I can't figure out how to fix.  I have a package that I wanted to create on my database with a wrapped body.  I mistakenly omitted the word "BODY" from the create or replace statement for the package body.  Running the script that way created the package in such way that it shows up as invalid but cannot be dropped.  The package shows up in queries to user_objects, etc but when attempting to drop the package I get ora-04043 not exist errors.

Anyone ever seen this before and know how to drop that corrupted package?

Select * from user_objects where object_type = 'PACKAGE'

OBJECT_NAME = 'POSTING_PROCS'
SUBOBJECT_NAME = NULL
OBJECT_ID =  134412
DATA_OBJECT_ID =    NULL
OBJECT_TYPE = 'PACKAGE'
CREATED = 12/29/2014 9:57:32 PM
LAST_DDL_TIME = 1/22/2015 11:08:52 AM
TIMESTAMP = 2015-01-22:11:08:52
STATUS =    INVALID
TEMPORARY = N
GENERATED = N
SECONDARY = N
NAMESPACE = 2
EDITION_NAME = NULL

in addition to not being able to drop the package I also can't run another create or replace because I get an error that the object already exists

select dump(object_name) from dba_objects where object_id=134412:

Typ=1 Len=13: 80,79,83,84,73,78,71,95,80,82,79,67,83

More info - I can run a create or replace for the package spec, but get the object with that name already exists error when doing create or replace package body


texts: SQL queries break our game! (Back-end server is at capacity)

texts:
We have a Facebook game that stores all persistent data in a MySQL database that is running on a large Amazon RDS instance. One of our tables is 2GB in size. If I run any queries on that table that take more than a couple of seconds, any SQL actions performed by our game will fail with the error:

HTTP/1.1 503 Service Unavailable: Back-end server is at capacity

This obviously brings down our game!

I've monitored CPU usage on the RDS instance during these periods, and though it does spike, it doesn't go much over 50%. Previously we were on a smaller instance size and it did hit 100%, so I'd hoped just throwing more CPU capacity at the problem would solve it. I now think it's an issue with the number of open connections. However, I've only been working with SQL for 8 months or so, so I'm no expert on MySQL configuration.

Is there perhaps some configuration setting I can change to prevent these queries from overloading the server, or should I just not be running them whilst our game is up?

I'm using MySQL Workbench to run the queries.

Here's an example....

SELECT *
FROM BlueBoxEngineDB.Transfer
WHERE Amount = 1000
AND FromUserId = 4
AND Status='Complete';

As you can see, it's not overly complex. There are only 5 columns in the table.

Any help would be very much appreciated - Thanks!


texts: How to get average of views before clicking on advertisement?

texts:
I've got two tables containing information about viewing and clicking on advertisement by each user:

Clicks:

userID   Action   Time
123      c        2016-01-08 01:57:00
123      c        2016-01-11 03:17:12
200      c        2016-01-09 02:20:10
332      c        2016-01-12 07:07:07

Views:

userID   Action   Time
123      0        2016-01-07 01:33:00
123      0        2016-01-10 04:12:13
200      w        2016-01-08 02:20:10
332      0        2016-01-09 07:07:07
332      0        2016-01-10 07:07:07
332      0        2016-01-11 07:07:07

I want to know what was the average of views before clicking on advertisement first time, second time etc. From the example above: User 123 clicked first time having only one view before, user 200 also, and user 332 had three views before clicking first time. So the average for the first click is (1+1+3)/3=1.67. Only user 123 clicked twice, after two views (in total).

The result I want looks like this:

Nr_clicks  Avg_views
1          1.67
2          2
...

The Action column in Views table can take different values (but not c), in Clicks table is always c. I was trying to use JOIN with subquery and then average results but I get only some errors. I'm working in Hive.


texts: Oracle no privileges on tablespace USERS

texts:
I have a brand new Oracle database that is giving the error:

ORA-01950: no privileges on tablespace 'USERS'

I have done:

alter user kainaw quota 100M on 'USERS';
grant unlimited tablespace to kainaw;

Still, a single insert returns that error. Other than disk quota, what else causes the "no privileges on tablespace 'USERS'" error?

UPDATE:

Oracle version is 11.2.0.3.0 (11g). I am logging in from the command prompt on the server. So, I alter user kainaw as sysdba. Then, I logout and login a user kainaw to test:

insert into i.test values (1);

Note: i.test is a table with only a number field. I get the error above. I logout as kainaw, login as sysdba, play with permissions, logout, login, test, error, logout, login, ...


texts:
I just ran across a query:

INSERT [TableName] SELECT * FROM [TableName]

Note that it is the same table. This query causes serious blocking on one of my test databases (I wanted to drop it but couldn't and this made me investigate the issue).

The lock type is LCK_M_X. I cannot kill the session - i get the following message:

  PID 56: transaction rollback in progress. Estimated rollback
  completion: 27%. Estimated time remaining: 5715216 seconds.

I don't want to wait the estimated 2 months for the rollback to complete.
The transaction isolation level of the database is READ COMMITED.

Is it possible to kill this session? How should you act if you find such a query in one of the mission critical databases?


texts:
I am trying to migrate this node library https://github.com/LocalSEOGuide/lighthouse-reporter from Postgres to MySQL and getting an error  error: ER_TRUNCATED_WRONG_VALUE: Incorrect datetime value: '2020-03-22T20:38:48.347Z' for column 'fetch_time' at row 1

I've isolated the query where the error happens and built a little test:

     await db.query('drop table test');
     await db.query(`CREATE TABLE IF NOT EXISTS
                  test(
                    id SERIAL PRIMARY KEY,
                    fetch_time datetime
                  );
    `);

    const query_text = "INSERT INTO test SET ?"
    const query_params = { "fetch_time":"2020-03-22T20:38:48.347Z" }
    await db.query(query_text, query_params);

It seems that the reporter is returning a timestamp with a Z at the end, but i cant figure out how to get it properly stored in to mysql.  Any suggestions would be very helpful!

I've also tried with fetch_time as a timestamp


texts: How using many tables and JOINS affect performance vs. using limited number of tables and lesser JOINS?

texts:
I want to design a db schema concerning a schedule.

I. I can use all fields in the same table, for example:

id | shop_id | mo (varchar) | tu (varchar) | we | th | fr | sa | su
 1 | 1       | 08:00-12:00  | ...

II. or I can use more tables one for schedule with structure:

id | shop_id | mo_id | tu_id | we_id | th_id | fr_id | sa_id | su_id

and then one table for each day... for ex: monday with structure:

id | form_time | to_time

III. or I can use one table for schedule with structure:

id | shop_id | weekday_id

and then only one table for weekdays with structure:

id | day | form_time | to_time

Which structure of these 3 structures you think is better in performance?


texts:
I'm fairly new to working with databases and am trying to fully understand the purpose of a tablespace.

Lets say I have a system where a user will input a schedule of tasks that need to be performed. This schedule is stored in my database and sent to a process which will distribute the tasks. As tasks are completed, the results are sent to a separate process which will store the raw result data, correlate the results, store a correlation summary, and then send it out.

Would I keep all of this data in a single tablespace or would it make more sense to separate it into two tablespaces - one for the schedules and one that will store the raw results and correlated result summary?

In general, how related should tables/indexes be to be stored in the same tablespace?


texts: How to relate Agencies at the State, County, and Local Government levels

texts:
I'm having a bit of trouble deciding how to properly relate government agencies to their respective state, county, and local government levels.

I would like to be able to query for agencies that are specific to the level requested. For example, user requests all state level agencies for the state of Florida.

I would also like to be able to query for all agencies for any combination of levels requested.  For example, user requests all county level agencies specifically for Orange County AND all local level agencies (cities, towns, villages, etc) found in Orange County.

That being said, looking at my database diagram that I have so far (attached below), would "junction" tables be the way to go to properly reference agencies to their respective governments to make those queries possible?

Current database diagram:

Any help or pointers in the right direction will be greatly appreciated!

Responses to comments:

  Be cautious about assuming that the flow will always be Locality → County → State. There are cases of cities (localities in your diagram) that are not contained in any county. See https://en.wikipedia.org/wiki/Independent_city_(United_States). So you'll want to include a mechanism for having a locality belong directly to a state.

Interesting, thanks for sharing. Perhaps the easiest way to account for those scenarios of independent cities would be to allow a NULL county_id in the FK of the localities table? What do you think?

  If you use a NULL county_id, how will you connect an independent city to it's state? Could you add a state_id FK to the localities table, and allow one or the other of it and county_id to be NULL, but not both?

I should be able to. It had crossed my mind to make state_id a FK in localities along with county_id, but because I wasn't aware of the existence of independent cities in the U.S., I decided not to include it at the time. I will add that additional FK to the design, and only allow for the county_id to be NULL.

  What are those id columns for?

I made the id column in each table serve as both a PK and identity that self-increments to allow me to relate each table. I will look into using the codes provided by the ISO standard.

  Is this database meant to contain data about a one or more countries?

Only focused on U.S. currently.


texts: How to retrieve / read SQL Image Data which is stored in the database?

texts:
From one application, we are storing all images in SQL database in the Image datatype. From another application, i need to retrieve the images and display it on User Interface.

I am able to store the images. I am not able to read the images.

How to retrieve / read SQL Image Data which is stored in the database ?


texts: Restore a WordPress database from .frm and .idb only

texts:
I had a WordPress website running a long while ago. I used WAMP server. Then, I uninstalled WAMP. Today I would like to work again on my website; all that remains of my database is this (I cannot find the backup I once made) the ibdata1 file is not the right one so the only thing I have to eventually restore the database are.frm and .idb (yes, I really, really screwed up) :

So, there seem to be some data left.

However, here is what I get in phpMyAdmin:

It says my tables don't exist (no table were found for wp_db). Is there any way I can restore the database?


texts:
My database in sql server 2008 has marked suspect.

How can I fix this problem?


texts: Can not give NT SERVICE\MSSQLSERVER permissions on network drive

texts:
I have an SSIS package that I would like to run as a job. The flat file source sits on Server A while the package and job sit on Server B.
When I run the job that executes the SSIS package I get an error (Error code 0xC020200E Cannot open the datafile) but I can manually execute the deployed package in SSMS and it executes successfully . I had found out that this was caused by the Server B's NT SERVICE\MSSQLSERVER service account not having access to the folder on Server A. To test this I had created a test folder on Server B and made the SSIS package look there, I have then given NT SERVICE\MSSQLSERVER full access to the folder on Server B. The job executed successfully and the package ran however when I try to give NT SERVICE\MSSQLSERVER permissions to the folder on server A, I can not find the server in the locations tab and I cannot access the NT SERVICE\MSSQLSERVER service account. I have tried mapping the network drive however that did not help. The files are automatically uploaded onto server A every morning, the folder has to stay on Server B. Any assistance or advise is appreciated.


texts: Why is a Shared lock blocking on an IX lock when Read Committed Snapshot is enabled?

texts:
Our system has randomly started deadlocking in areas where it previously had not, and an analysis of the deadlock graph looks like it should not even be happening to begin with. Here is a screenshot of the deadlock graph.

For reference: The left node is a basic insert into the sync_entities table, where the right node is:
update sync_entities
set A=B, C=D
from sync_entities e
join sync_revisions r on e.id=r.entity_id
where r.id in (<some very large list of IDs>)

Based on my understanding of lock compatibility, requesting an S lock when an IX lock exists would block only if Read Committed Snapshot is off, but it is turned on for this database, and has been for a very long time. Am I missing something?
The deadlock XML says that both processes were using isolationlevel="read committed (2)".


texts: How can I receive an input string parameter having about 200000 characters?

texts: I have a SP in Oracle 11G R2 that receives an input XML that contains about 200000 characters that are extracted from the xmliside the SP and inserted into about 25 tables. Thus I am not being able to use VARCHAR2 or CLOB. How can I handle such huge data??


texts: MySQL workbench can't deal with MariaDB's new Invisible columns

texts:
Recently upgraded to MariaDB 10.3 which has a new feature whereby we can create an invisible column like so:

ALTER TABLE MyTable ADD COLUMN `TestA` INT INVISIBLE;

But once I create this column, MySql Workbench will error on this table. Is there any way to workaround this? Or is there another GUI tool that can deal with this new column type?


texts: Triggering a shell script from MySQL without any 3rd party libraries

texts:
Is it possible to call a bash script file in linux using a trigger in MySQL?
I've been searching for the answer online and I ran into this articles:

http://patternbuffer.wordpress.com/2012/09/14/triggering-shell-script-from-mysql/

http://crazytechthoughts.blogspot.com/2011/12/call-external-program-from-mysql.html?showComment=1416010000049#c2028082587789719436

But I was wondering if it's possible to do it without any 3rd party libraries like UDF for example. Any help is appreciated.


texts: Inspecting the status of a long-running COPY statement

texts:
I need to migrate a table from one PostgreSQL database to another. There was a chance I would need to fix some data, so I exported to a CSV. Then I imported the CSV in the second database with a COPY statement.

This process has been running for 5 days now. The only way I found to inspect its progress was to compare the sizes on disk. The original table was 95 GB (from psql's \dt+), and the CSV was 40 GB. So I thought I could compare the new table size with those numbers. I thought that the new table would stop at 95 GB, or even before. Instead, it's now at 103 GB and who knows when it will stop.

Of course, select count(*) does not work because the copy happens in its own transaction so the rows are shielded until it's done. But I know that the the table has about 1500 million rows. So if somehow I could get an estimate of the number of rows currently in the new table I could compare.


texts: SQL Server DML auditing - Triggers vs SQL Server Audit Feature (vs temporal tables vs change data capture)

texts:
I am looking to implement an auditing process on an instance I have on SQL Server 2017. However, I am not finding a complete conclusion on which auditing feature would be best to use for a DB with a lot of transactions. I only need to audit the DML statements and not the values (e.g. I would need to know about an UPDATE statement and who performed it and on which table but I do not need to know what the value of the column was before the update).
From my research I have excluded traces and extended events due to their performance impact.
I have also excluded change tracking since it cannot be used with partial containment and I have some DBs which are set to partial containment.
I am currently mostly between using SQL Audit feature (i.e. using server and database audit specifications) and triggers.
I do understand that both the above methods will cause some overhead and that the performance impact depends on how much activity I am auditing, but given the same activity being audited (e.g. selects, inserts and updates on 10 tables), which one will cause the least performance impact between these two? I am planning to use an audit file (locally or on a separate fileshare) in case I use the SQL Audit feature (and not in the windows log file).
From my research I have also concluded that triggers have to be maintained with every schema change as well, so if SQL Audit feature does not cause a massive difference in performance (compared with triggers) I will go with it.
Also I am not sure whether change data capture or temporal tables will be a better solution compared to SQL Audit feature/triggers.
Any experience with these auditing methods and their performance impact would be greatly appreciated as information about this is highly limited.


texts: How to wait for PostgreSQL to be startable / restorable?

texts:
I'm testing a PostgreSQL 8.2.1 to 9.2 upgrade on a virtual machine running a custom Linux distro. The upgrade procedure is as follows:

Start the pg service
Vacuum all DBs (not sure if this is needed)
Backup with pg_dumpall
Stop the pg service
Move away the directory where the data is stored (/var/pg; it's a simple, single-server setup)
Install PostgreSQL 9.2
initdb
Start the server
Restore the dumped data
reindexdb all DBs
Recreate the referential_constraints view
Vacuum all DBs (AFAIK required after this upgrade)

This procedure works fine on one host, backing up and restoring without a hitch. On another machine with a different database points 1 through 7 work fine, but the server won't start unless I add a sleep 1 after initdb, and even then the dumped data can't be restored because "the database system is starting up". What are the standard ways to deal with this, except for these terrible hacks:

sleeping for some generous amount of time before either operation,
looping until it works or until a generous timeout is reached, or
looping until it accepts a trivial query or a timeout is reached.

Edit: The "solution" didn't work after all. What does it take to make sure the database is ready to run a restore?


texts: I'm having trouble on starting MariaDB instance. Here is the error I got: https://pastebin.com/RsdMxYRY. Any help is appreciated. Thanks.


texts: Can I shed permissions during a session?

texts:
I am creating a reporting server which will run certain reports.

The reports are created by me and saved as SQL files.

The reporting application has read/write access, for good reasons.

Is there a way I can deprivilege a SQL session so that when executing the SQL it will use less privileges for execution than the application has available?


texts:
I have several views that are used to export data from SQL-Server into .csv files. They are all executed via SSIS packages. Our DBA has decided to set the "Max Degree of Parallelism" to 1 and told me to use MAXDOP where I think it makes sense.

I have now seen many times that MAXDOP really helps to pull big data amounts (especially when it results in a table scan over all partitions).

Questions

How can I use MAXDOP in a view ?
Is there a way around this restriction ?


texts: MobiLink: Where is Sybase Central storing the actual content of an remote task for mlagent

texts: When creating a remote task inside an MobiLink project with Sybase Central where it is storing the actual content of task (e.g. executing SQL -> Query which shall be executed)


texts: How to reduce sysaux tablespace size with Audit Trail Unified enabled

texts:
I have Oracle 12c Standard instance with dev database with enabled Audit Trail Unified.

And now SYSAUX tablespace has size 32Gb, but all data is only 9Gb.
When I tried to reduce size of SYSAUX I received an error message:

ORA-03297: file contains used data beyond requested RESIZE value

Then I saw that some big sized objects in this tablespace:

I've executed the following scripts:

BEGIN
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
   audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
   last_archive_time => SYSDATE-1,
   rac_instance_number => 1 /* single instance database */),
   container            => DBMS_AUDIT_MGMT.container_current;
END;
/

BEGIN
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
use_last_arch_timestamp => TRUE);
END;
/

After this in AUDSYS."CLI_SWPXXXXXXX" table I don't see any rows.
But it still have the same size .

Also any attempts to shrink this table or move it to another tablespace give me an error message:
ORA-55941: DML and DDL operations are not allowed on table "AUDSYS"."CLI_SWPXXXXXXX"

So, my question is: can I make size of SYSAUX tablespace smaller or move this audit tables to another tablespace?

Update: by this link: click I've found info that "Moving CLI.SWP possible only for Enterprise edition, for standard impossible due partitioning." Is that true? It seems like my instance is Standard...


texts: I'm working with application that can have millions of data. Just wondering if it is ok to use auto_number as primary key. I have read that bigint, int etc has limit. So for example site is on live for 10 years. Is it ok to use auto_number in it. Does big websites like facebook also use auto_number as their primary key?


texts:
I have an application where several devices send data every second to my server. On the database I have a table with the devices and another table with the data sent every second.

The first time a device sends the data the server script should register (INSERT) that device id in the device table and add the data to the data table.

My question is, what would be faster?

To do a SELECT EXISTS query to determine whether an INSERT is required on the device table. If SELECT EXISTS returns false then execute an INSERT, otherwise do nothing.
To execute always an INSERT statement. Considering the device id is primary key on the table, if the device already exists it would return an error, otherwise it would insert it.

Only the first time a device sends data would require an actual INSERT, after that no INSERT would ever be required.

The PostgreSQL version is 9.4.

The table of devices is defined as follows:

CREATE TABLE common.tag
(
  customer integer,
  hostname character varying(150),
  description text,
  model integer,
  configprofile integer,
  id character varying(150) NOT NULL,
  host integer,
  CONSTRAINT tag_pk PRIMARY KEY (id),
  CONSTRAINT tag_fk_client FOREIGN KEY (customer)
      REFERENCES common.customer (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT tag_fk_configprofile FOREIGN KEY (configprofile)
      REFERENCES common.configprofile (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT tag_fk_tagmodel FOREIGN KEY (model)
      REFERENCES common.tagmodel (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)


texts: Index planning for many columns with different usage

texts:
I've a table with 80 columns and that is a base table for most of the application. Daily load inserts almost 8,000 records and update upto 2,000 records. This table is now having more than 5 million records. Unfortunately, I can't change the architecture and for next few months I have to continue with this.
Now, as I said there are multiple application connected to the table which are fetching data from it and almost all of them using different columns for their purpose. Example of few of those queries are:

SELECT Col1, Col2
FROM Table
WHERE Col3 = 'something'

SELECT Col4, Col5, Col6
FROM Table
WHERE Col7 IN ('A','B') AND Col1 = 'something'

SELECT Col1, Col2, Col7, Col28, ....
FROM Table
WHERE Col1 = 'Something' AND Col2 = 12 AND (Col2 > 2 OR Col7 <20)

As you can see that one column is being used in where clause and some query it is in select clause. I have created indexes and now I realized that I need more but that doesn't seems to be feasible to me as I will end up in having so many indexes.

  How can I implement index strategy in this kind of scenario?

Also,

  How to design index when those columns are coming up in different
  combinations?

Ex.:

SELECT Col1, Col2, Col3
FROM Table
WHERE Col3 = 'something' AND Col4 = 'something'

SELECT Col4,Col1, Col5
FROM table
WHERE Col3='Something'AND Col7 = 'something' AND Col67 = 'something'

out of 80 columns, application is using > 50 columns in where clause.


texts: Entry inside audit log table for CUD operations on tables

texts:
I have a DB e.g "dbAuditLogs" inside created tables like as mstUser,mstEmployee,mstDepartment and auditHistory. when any CUD operation performed on mstUser/mstEmployee/mstDepartment then want to register an entry inside auditHistory table. I can do this with the help of triggers but in future when number of tables increase or columns removed from existing table then we will need to make changes into TRIGGER as well. Also by creating multiple TRIGGERS db performance will become slow.
As per my understanding below are my queries

Is there any design pattern available like as "UnitOfWork" in
MYSQL/NODEJS.
Is there any npm available which can handle Audit log
    from API.
Only trigger is the best option suited for above
    condition?

Trigger created by me

BEGIN
SET @json = "{";
SET @first = true;
IF (OLD.firstName!=NEW.firstName) THEN
  SET @first = false;
  SET @json = CONCAT(@json, "\"firstName\"", ":", "\"", NEW.firstName, "\"");
END IF;
IF (OLD.lastName!=NEW.lastName) THEN
  IF (!@first) THEN
    SET @json = CONCAT(@json, ",");
  END IF;
SET @first = false;
SET @json = CONCAT(@json, "\"lastName\"", ":", "\"", NEW.lastName, "\"");
END IF;
SET @json = CONCAT(@json, "}");
INSERT INTO mstAuditHistory (tableName,jsonData,recordId,userId,eventType)
    VALUES ('contactDetails',@json, NEW.id, NEW.userId,'Update');
END


texts:
I am new to information systems and was working on a homework. I am not sure of the difference between physical and logical data independence. What is meant by physical and what is meant by logical ?

I keep thinking of physical as like a harddrive or something but its not. Is it more like a data structure ? So what is logical ?


texts:
here is the scenario:

There are two schema created in oracle 10g, say db 1 and db 2. db1 can be accessed by username : sys with password and db2 can be accessed by username : test with password.

How is it possible to access db1 using username : test with password?
In MSSQL there is an option for user mapping to different database. is there any such options in oracle 10g to do the same?


texts: Performance in a single SQL Server 2014 database is slower than the rest

texts:
This is an interesting one... I've inherited a SQL Server for our ERP system. It currently runs SQL Server 2014 CU 10. I've done some best practices and performance tuning on it and server performance has gone from the toilet to acceptable levels.

However, I have a query/stored procedure that when run on one specific database takes up to 7 minutes to run while on similar databases (there are 6 others, 1 has MUCH more data) it takes maybe 30 seconds tops. I've checked file permissions, I've diff'ed the SP's, I've checked database settings and I can't find anything that points to the slowness.

Does anyone have any recommendations on what I should be looking at?

I was able to get an execution plan excerpt from the databases (2MB limit on Brent's site).

This is the execution plan on the "bad" database.
This is the execution plan for the "good" database.

The only difference between the two is the "good" DB doesn't have the current data (i.e. older copy).

I've double checked my server settings in both environments & they appear to be identical. The only thing I can confirm so far is if I drop the "bad" database's compatibility level down to 2008 it performs perfectly. Yet the same database inside the dev environment at 2014 runs flawlessly.

There is a possible coding error in the stored procedure I'm checking. I can't get anything out using CTRL+L but I do if I attempt to run it.

The databases are not replicated. The server is a VM and the drives are all shared.
I've done a table comparison & they are identical. Compatibility level is the same as well as are indexes and stats.

I'll update again when I know more.


texts: why my split string functions are not working with large OBJECT_DEFINITION?

texts:
I have in one of my databases a stored procedure

called PROCEDURE [repl].[usp_upd_repl_out_application], that is not exactly a very clever stored procedure but it has over 389 lines.

As I need to change this stored procedure, I like to back it up to a table before any alteration, I am aware of source control applications and that should be the way to go, but I am happy doing things my way and it works for me.

it might be another case of re-inventing the wheel, but I still would like to know why my split strings functions are failing.

So while attempting to backup this stored procedure
this stored procedure is object_id = 146815585 in my_database.

this works perfectly:

DECLARE
  @olddelim nvarchar(32) = char(13) + Char(10),
  @newdelim nchar(1)     = NCHAR(9999); -- pencil (✏)
SELECT *
  FROM STRING_SPLIT(REPLACE(OBJECT_DEFINITION(146815585)
  , @olddelim
  , @newdelim)
  , @newdelim);

this also works nicely, although it is not the way I want it:

if object_id('tempdb..#radhe') is not null
   drop table #radhe

create table #radhe(i int identity(1,1) not null primary key clustered, ln nvarchar(max))
insert into #radhe (ln)
exec sp_helptext '[repl].[usp_upd_repl_out_application]'

I have got this split function from here:

ALTER FUNCTION dbo.fn_TVF_Split(@arr AS NVARCHAR(max), @sep AS NCHAR(1))
RETURNS TABLE
WITH SCHEMABINDING,ENCRYPTION
AS
RETURN
WITH
   L0 AS (SELECT 1 AS C UNION ALL SELECT 1)       --2 rows
  ,L1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B)    --4 rows (2x2)
  ,L2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B)    --16 rows (4x4)
  ,L3 AS (SELECT 1 AS C FROM L2 AS A, L2 AS B)    --256 rows (16x16)
  ,L4 AS (SELECT 1 AS C FROM L3 AS A, L3 AS B)    --65536 rows (256x256)
  ,L5 AS (SELECT 1 AS C FROM L4 AS A, L4 AS B)    --4,294,967,296 rows (65536x65536)
  ,Nums AS (SELECT row_number() OVER (ORDER BY (SELECT 0)) AS N FROM L5)
SELECT
(n - 1) - LEN(REPLACE(LEFT(@arr, n-1), @sep, N'')) + 1 AS pos,
SUBSTRING(@arr, n, CHARINDEX(@sep, @arr + @sep, n) - n) AS element
FROM Nums
WHERE
    n <= LEN(@arr) + 1
    AND SUBSTRING(@sep + @arr, n, 1) = @sep
    AND N<=1000
GO

DECLARE
@olddelim nvarchar(32) = Char(10) -- char(13) + Char(10),
,@newdelim nchar(1)     = NCHAR(9999); -- pencil (?)
SELECT m.*
--INTO TableBackups.dbo._MMiorelli_20180305_repluspupdreploutapplication_140331_DB_ID_32
from  dbo.fn_TVF_Split( OBJECT_DEFINITION ( 146815585),  @olddelim) m

it only returns 18 rows out of the 398

and this one below, only returns 60 out of the 398.

--=============================================
-- this is the SplitString function
-- for use when we're below sql 2016
--=============================================
alter FUNCTION dbo.SplitString

    (
        @List NVARCHAR(MAX),
        @Delim NVARCHAR(255)
    )
    RETURNS TABLE
    WITH ENCRYPTION
    AS
        RETURN ( SELECT [Value] FROM
          (
            SELECT
              [Value] = LTRIM(RTRIM(SUBSTRING(@List, [Number],
              CHARINDEX(@Delim, @List + @Delim, [Number]) - [Number])))
            FROM (SELECT Number = ROW_NUMBER() OVER (ORDER BY name)
              FROM sys.all_objects) AS x
              WHERE Number <= LEN(@List)
              AND SUBSTRING(@Delim + @List, [Number], LEN(@Delim)) = @Delim
          ) AS y
        );

this is how I call this function:

 DECLARE
@olddelim nvarchar(32) = Char(10) -- char(13) + Char(10),
,@newdelim nchar(1)     = NCHAR(9999); -- pencil (?)

SELECT m.*
from  master.dbo.splitstring( OBJECT_DEFINITION ( 146815585),  @olddelim) m

returns 60 rows.

question:

is there a way I could modify my function master.dbo.splitstring so that it would return the whole 398 lines of the procedure?


texts: MySQL - replicate some tables between transaction and report database

texts:
I have two different database (on two different hosts). One database is transactional and another database is reporting. Let us say, it stores information about departments and their employees.

Transactional Database has one department table which stores employee data of all departments. This table has foreign and primary keys. This table has no partitions. Disk size is above 250 gb.

Reporting Database has one department table which is partitioned by department id. This table has no primary or foreign keys. Otherwise, structurally this table is same as transactional one.

Is there any way to replicate the department table between transactional and reporting.


texts: Online index rebuild snapshot location and impact of filling index drive

texts:
My understanding of a rebuild index online operation is that the index will have a snapshot taken and the rebuild is started on the snapshot index.

My question is where does the snapshot index reside?

Our databases have data, log and index file drives. My understanding is the snapshot should reside on the index file drive. Is this correct?

In addition what happens if the index drive runs out of space? Anyone had experience of this? It wont be an issue but I would like to know!

Version Info: SQL Server 2005 9.00.4266.00(x64) EE


texts: Would SQL in a cluster failover when transaction log for database is full?

texts:
I have a Microsoft SQL Cluster with one instance. If one of the databases is full, would SQL in a cluster failover when transaction log for database is full?

If it depends on the reason the transaction log is full please refer to following::

due to 'log_backup'
due to 'availability replica'
active_transaction
'replication'
'CHECKPOINT"